I have a fairly complex query which results in a list of Customer Ids where they have not ordered within a defined period, have no Rep assigned to them, and are business customers rather than private. This simply returns a sequential list of Ids which match the BIGINT primary keys of the matching customers, l…
Tag: join
INNER JOIN with aggregate functions in my SELECT
I’m trying to join a new column to my current query that uses aggregate functions. I create this column with a new query that also uses an aggregate function from a different table but I’m not sure if a JOIN will work for me since I need to join it to its respective row. TABLE A (employees that ar…
how join laravel tables to get non intersected data
in my Laravel app I have 3 tables : users, documents and type_documents, the user have multiple documents and document have one type_document I want select the types that are not used in documents table for the current user with eloquent I try with this, but it give me the used type_documents : I use Laravel …
MySQL to fill in the blanks with NULLs when their isn’t a row based on set number of IDs from the join
Looking to fill in the blanks with NULLs when there isn’t a record that is for a row that I am expecting. Consider the following wp_posts (p) ID post_title 1 Week 1 2 Week 2 3 Week 3 4 Week 4 5 Week 5 wp_users (u) ID user_email 1 email1@example.com 2 email2@example.com 3 email3@example.com 4 email4@exam…
MySQL – Join 2 tables and count number of entries
I’m trying to join 2 tables and count the number of entries for unique variables in one of the columns. In this case I’m trying to join 2 tables – patients and trials (patients has a FK to trials) and count the number of patients that show up in each trial. This is the code i have so far: Th…
How to use INNER LEFT instead of EXCEPT?
I read that MySQL does not support EXCEPT, and the workaround is to use LEFT JOIN. THIS IS MY QUERY: Basically: Trying to find out the manufacturers that sell PCs but not laptops. How can I convert this query with the LEFT JOIN? I got confused.. Table Computers: Table Manufacturers Computers: So since Manufac…
How would I make my SQL statement show only the first line (lowest price + total purchases)
I’ve got this SQL exercise I’m trying to complete but I can’t figure out how to specifically show one row that should contain the media type with the lowest # of purchases and purchase value. I’m completely unsure of what I should add after my ‘group by’? So this is what I …
Counting orders by supplier results in SQL error
I am trying to get how many orders each supplier have, by showing two columns, CompanyName and number of orders. And then Sorting them by descending order. My SQL: But I get the following errors: Msg 145, Level 15, State 1, Line 65 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.…
How do I solve the error Ambiguous column name with join
I’m currently struggling with a join where the column [NAME] results in an error (ambiguous column). I’m pretty new at SQL and someone else made the first join for me, so I now just tried to replicate this with something else I wanted to join and as I understood, I do need to add the columnname of…
SQLite: how to exclude records in a database with join tables?
I have a database with a schema similar to: Say they have the following data in them: Artists: artist_id artist_name 1 Bob 2 Shawn Songs: song_id song_name 1 one song 2 another song song_artist: song artist 1 1 1 2 2 2 Here, both Bob and Shawn are involved in one song, and only Shawn is involved in another so…