Solved

How would I write this SELECT statement?

Posted on 2016-08-23
6
46 Views
Last Modified: 2016-08-24
Here's what I've got in my table right now. Take note of the DISTINCT door_id's (389, 287)

shopping cart
I've got another table called doors. Each door id has a corresponding "feature_door_name." I need to write a SELECT statement that starts off with something like this:

select DISTINCT door_id from cart where session_id='i2rnvaoealphebajh9e0jsipb7'

..and do a JOIN of some kind that allows me to retrieve the feature_door_name. That data looks like this:

table
What do you think?
0
Comment
Question by:brucegust
6 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Either INNER or LEFT JOIN, based on your requirement.

SELECT t1.*, c.feature_door_name
   FROM your_table t1
   JOIN cart c ON t1.door_id = c.door_id

Open in new window


SELECT t1.*, c.feature_door_name
   FROM your_table t1
   LEFT JOIN cart c ON t1.door_id = c.door_id

Open in new window

0
 

Author Comment

by:brucegust
Comment Utility
When I do this:

select distinct c.door_id, d.feature_door_name from cart c INNER JOIN doors d on c.door_id=d.id where c.session_id='i2rnvaoealphebajh9e0jsipb7'

I expect to get to "featurenames" based on the fact that I've got two distinct door_ids in the cart, but I only get one.
0
 

Author Comment

by:brucegust
Comment Utility
This:

SELECT c.*, d.feature_door_name FROM doors d LEFT JOIN cart c ON d.id = c.door_id and c.session_id='i2rnvaoealphebajh9e0jsipb7'

...gives me this:

too many
I'm missing something. I need to see:

Brandywine Shaker
Ginger Deluxe

..that's it.

What have I got to do?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 49

Accepted Solution

by:
Ryan Chong earned 250 total points
Comment Utility
try this
SELECT c.door_id , d.feature_door_name 
FROM cart c LEFT JOIN doors d ON d.id = c.door_id 
Where c.session_id='i2rnvaoealphebajh9e0jsipb7' 
group by c.door_id , d.feature_door_name

Open in new window

0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 200 total points
Comment Utility
try other way around for LEFT JOIN.
SELECT c.* , d.feature_door_name 
FROM cart c LEFT JOIN doors d ON d.id = c.door_id 
Where c.session_id='i2rnvaoealphebajh9e0jsipb7' 

Open in new window

0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 50 total points
Comment Utility
Since you start with the doors, you get all doors. Depsite of selecting all door fields as right fields in your field list, it's the "left" table the join starts from, therefore all the NULL rows for doors not having a cart entry. Simply start any query from the main table you want to query and join side data to this, not the other way around.

You might do the RIGHT JOIN instead, but I have to go far back to look where I ever needed this, it's typically only needed in conjunction with at least three tables, as you can of course always switch the order of two tables to make the join a left join. What matters is the order in which tables  are joined in the query, not the orientation of columns in the result.

Bye, Olaf.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now