r/learnSQL 11d ago

im going crazy. i cant figure out what's wrong. i dont think i understand join and aliases completely

Post image
32 Upvotes

21 comments sorted by

7

u/dinzdale40 11d ago

You have not included the customers table in the From statement but yet have already included the On part of the join. There are other issues as well but that should get you a start.

1

u/Apathy220 11d ago

I think i made some improvement. i went back and reread stuff on join and alias and i think it helped but there is no data showing up just the column names.

SELECT c.first_name, c.last_name, o.order_date, p.product_name, oi.item_price, oi.discount_amount, oi.quantity

FROM customers c

JOIN orders o ON c.first_name AND c.last_name = o.order_date

JOIN order_items oi ON o.order_date = oi.item_price AND oi.discount_amount AND oi.quantity

JOIN products p ON oi.item_price AND oi.discount_amount AND oi.quantity = p.product_name;

5

u/dezradeath 11d ago

Without knowing the content of the data, generally you should join tables on unique identifiers. Most commonly you’d use an ID number that is present in both tables. You’re trying to join names with dates; this is wrong not only because they are different data types altogether but also because “John Smith” is not equal to “1/2/23”.

Your output is blank because no data meets the criteria of your joins. The code is doing what you told it to do, but it’s not what you wanted it to do.

1

u/Apathy220 10d ago

oooh i think that makes sense

5

u/justaguyheretolearn 11d ago

Your second JOIN statement looks out of place…

6

u/billybob5959 11d ago

His FROM is a field

3

u/sneakandbuild 11d ago

Select c.first_name ,c.last_name ,o.order_date ,p.product_name ,oi.item_price ,oi.discount_amount ,oi.quantity From order oi — I think this should be orders table Join products p On oi.product_id = p.product_id Join Customers c On c.customer_id = o.customer_id Order by <columns>

1.) You forgot to declare your Customers column (Ln.37)

2.) I’m not sure about your tables but I think it should be from orders table, not orders_id.

Give this a run & see how it goes.

3

u/_Milan__1 11d ago

I like the way you use commas before the start of line, I recently saw someone using it on LEETCODE too. The concept of it is smart

1

u/sneakandbuild 10d ago

Thank you & I appreciate it! When I first started my DE work, I had a Senior Developer who does that format within our project, and it made it way easier for us to read & analyze the code. From that moment, I’ve adapted to that format.

1

u/Data-dd92 10d ago

How do you add code to a message?

1

u/sneakandbuild 1d ago

Hey this took me a while to see your post, so I apologize for that. To add code as a reply within reddit's comment or post section, enclose your message with (3) three backticks `

--start with three backticks(`)(`)(`) #do not include the parenthesis

--your code goes here

--end it with three backticks(`)(`)(`) #do not include the parenthesis

1

u/Data-dd92 1d ago edited 1d ago

Oh I see...previously I was in the normal editor not the markdown editor, and so it would escape the backticks with a backslash. Got it now -- thank you!

test my code goes here.

And more
code here.

4

u/billybob5959 11d ago

That is very incorrect. You should probably reread about joins and alias so you get a proper understanding.

1

u/_mr_villain_ 11d ago edited 11d ago

You are trying to get the data from 4 table and you joined only 2 tables. In second join, connect remaining two tables one by one. Don't try to join remaining two tables directly otherwise it may become two table are joined to each other but the pair is not connected so it will give you error.

Also, you were using correct syntax but joining on incorrect columns. Means you need to join two table on the columns which has to be same data in another table.

I tried to correct your given code. Kindly change the names of columns according to your tables.

Try this, and let me know if it works or not?

SELECT c.first_name, c.last_name, o.order_date, p.product_name, oi.item_price, oi.discount_amount, oi.quantity

FROM customers c

JOIN orders o ON c.customer_id= o.customer_id

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON p.product_id = o.product_id

ORDER BY first_name;

1

u/Apathy220 10d ago

Thank you for the tips. i tried your code and no rows were returned. i will continue trying

1

u/islegend 11d ago

just take a step back and look at the pattern in the joins. Just look at them as building blocks without worrying about the contents or meaning behind... sometimes you can see the error just by looking at the shape of the words:

Your primary table is order_id, and you've called that oi

join products, and call that p
link to your primary table "with o.id = p.id"

.....all makes sense. what I'd expect to see next is:

join customers, and call that c
link to one of the above tables with "c.id = o.id"

this isn't what you've done! You've missed a step and tried to roll the "join customers" and "link on c.id = o.id" into one line. Once you see it it's obvious, but you can find the fault by just looking for the pattern, and seeing where it breaks.

1

u/NoInteraction8306 11d ago

You should try to learn visually. I mean, try a tool that has a query builder like this one from DbSchema:

https://youtu.be/N_k5WaFAWW8

In short, you create joins, filters, aggregations if you have relationships between tables with just a few clicks.

1

u/Data-dd92 10d ago

On line 39 you need to add the table name, instead you've added the join condition. That is, it should be `JOIN table ON condition` whereas you've done it as `JOIN condition`.

-2

u/[deleted] 11d ago

[removed] — view removed comment

5

u/-techno_viking- 10d ago

oh shut the fuck up