Thread: Basic Query Question
Sorry for the basic question, I can't seem to figure it out. I have two tables, booking and bike. Both have bike_id as primary key. I am trying to return all bike_id's where bike.model='Kona' AND booking.booking_date DOES NOT EQUAL 'date'; My query is as follows: SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; Any help appreciated! Jude -- "None are more hopelessly enslaved than those who falsely believe they are free" -- Johann Wolfgang von Goethe
i'm not sure but i think not being explicit about the join causes psql to try to join on ID. What are you getting? an error or an empty result set? I would think you should do the join explicitly like this: select bike.bike_id from bike join booking on bike.bike_id = booking.bike_id where booking.date != '2011-11-20' On 12/7/11 3:55 PM, Jude Lucien wrote: > Sorry for the basic question, I can't seem to figure it out. > > I have two tables, booking and bike. Both have bike_id as primary key. > > I am trying to return all bike_id's where bike.model='Kona' AND > booking.booking_date DOES NOT EQUAL 'date'; > > My query is as follows: > > SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND > booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; > > Any help appreciated! > > Jude > > >
I've just realised why it may not be working - bike_id is not the primary key of booking. booking_id is. I was getting results, just not the correct ones. There are 23 bikes of a certain model, two of which are booked on a certain date. My query should then return 21 bike_id's. Can I even do a join if booking.bike_id is not the primary key? On 7 December 2011 21:02, Ramy Abdel-Azim <ramy.abdel-azim@startdatelabs.com> wrote: > i'm not sure but i think not being explicit about the join causes psql to > try to join on ID. > > What are you getting? an error or an empty result set? > > I would think you should do the join explicitly like this: > > select bike.bike_id from bike join booking on bike.bike_id = > booking.bike_id where booking.date != '2011-11-20' > > > On 12/7/11 3:55 PM, Jude Lucien wrote: >> >> Sorry for the basic question, I can't seem to figure it out. >> >> I have two tables, booking and bike. Both have bike_id as primary key. >> >> I am trying to return all bike_id's where bike.model='Kona' AND >> booking.booking_date DOES NOT EQUAL 'date'; >> >> My query is as follows: >> >> SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND >> booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; >> >> Any help appreciated! >> >> Jude >> >> >> > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice -- "None are more hopelessly enslaved than those who falsely believe they are free" -- Johann Wolfgang von Goethe
try it out and let us know. we anxiously await your reply ;-) On 12/7/11 4:15 PM, Jude Lucien wrote: > I've just realised why it may not be working - bike_id is not the > primary key of booking. booking_id is. > > I was getting results, just not the correct ones. There are 23 bikes > of a certain model, two of which are booked on a certain date. My > query should then return 21 bike_id's. > > Can I even do a join if booking.bike_id is not the primary key? > > > On 7 December 2011 21:02, Ramy Abdel-Azim > <ramy.abdel-azim@startdatelabs.com> wrote: >> i'm not sure but i think not being explicit about the join causes psql to >> try to join on ID. >> >> What are you getting? an error or an empty result set? >> >> I would think you should do the join explicitly like this: >> >> select bike.bike_id from bike join booking on bike.bike_id = >> booking.bike_id where booking.date != '2011-11-20' >> >> >> On 12/7/11 3:55 PM, Jude Lucien wrote: >>> Sorry for the basic question, I can't seem to figure it out. >>> >>> I have two tables, booking and bike. Both have bike_id as primary key. >>> >>> I am trying to return all bike_id's where bike.model='Kona' AND >>> booking.booking_date DOES NOT EQUAL 'date'; >>> >>> My query is as follows: >>> >>> SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND >>> booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; >>> >>> Any help appreciated! >>> >>> Jude >>> >>> >>> >> >> -- >> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-novice > >
That query doesn't work for me as I can't make seem to add "WHERE model='model'" to the query if I use a JOIN. On 7 December 2011 21:16, Ramy Abdel-Azim <ramy.abdel-azim@startdatelabs.com> wrote: > try it out and let us know. > we anxiously await your reply ;-) > > > On 12/7/11 4:15 PM, Jude Lucien wrote: >> >> I've just realised why it may not be working - bike_id is not the >> primary key of booking. booking_id is. >> >> I was getting results, just not the correct ones. There are 23 bikes >> of a certain model, two of which are booked on a certain date. My >> query should then return 21 bike_id's. >> >> Can I even do a join if booking.bike_id is not the primary key? >> >> >> On 7 December 2011 21:02, Ramy Abdel-Azim >> <ramy.abdel-azim@startdatelabs.com> wrote: >>> >>> i'm not sure but i think not being explicit about the join causes psql to >>> try to join on ID. >>> >>> What are you getting? an error or an empty result set? >>> >>> I would think you should do the join explicitly like this: >>> >>> select bike.bike_id from bike join booking on bike.bike_id = >>> booking.bike_id where booking.date != '2011-11-20' >>> >>> >>> On 12/7/11 3:55 PM, Jude Lucien wrote: >>>> >>>> Sorry for the basic question, I can't seem to figure it out. >>>> >>>> I have two tables, booking and bike. Both have bike_id as primary key. >>>> >>>> I am trying to return all bike_id's where bike.model='Kona' AND >>>> booking.booking_date DOES NOT EQUAL 'date'; >>>> >>>> My query is as follows: >>>> >>>> SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND >>>> booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; >>>> >>>> Any help appreciated! >>>> >>>> Jude >>>> >>>> >>>> >>> >>> -- >>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-novice >> >> >> > -- "None are more hopelessly enslaved than those who falsely believe they are free" -- Johann Wolfgang von Goethe
could you post the query you tried? On 12/7/11 4:20 PM, Jude Lucien wrote: > That query doesn't work for me as I can't make seem to add "WHERE > model='model'" to the query if I use a JOIN. > > > On 7 December 2011 21:16, Ramy Abdel-Azim > <ramy.abdel-azim@startdatelabs.com> wrote: >> try it out and let us know. >> we anxiously await your reply ;-) >> >> >> On 12/7/11 4:15 PM, Jude Lucien wrote: >>> I've just realised why it may not be working - bike_id is not the >>> primary key of booking. booking_id is. >>> >>> I was getting results, just not the correct ones. There are 23 bikes >>> of a certain model, two of which are booked on a certain date. My >>> query should then return 21 bike_id's. >>> >>> Can I even do a join if booking.bike_id is not the primary key? >>> >>> >>> On 7 December 2011 21:02, Ramy Abdel-Azim >>> <ramy.abdel-azim@startdatelabs.com> wrote: >>>> i'm not sure but i think not being explicit about the join causes psql to >>>> try to join on ID. >>>> >>>> What are you getting? an error or an empty result set? >>>> >>>> I would think you should do the join explicitly like this: >>>> >>>> select bike.bike_id from bike join booking on bike.bike_id = >>>> booking.bike_id where booking.date != '2011-11-20' >>>> >>>> >>>> On 12/7/11 3:55 PM, Jude Lucien wrote: >>>>> Sorry for the basic question, I can't seem to figure it out. >>>>> >>>>> I have two tables, booking and bike. Both have bike_id as primary key. >>>>> >>>>> I am trying to return all bike_id's where bike.model='Kona' AND >>>>> booking.booking_date DOES NOT EQUAL 'date'; >>>>> >>>>> My query is as follows: >>>>> >>>>> SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND >>>>> booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; >>>>> >>>>> Any help appreciated! >>>>> >>>>> Jude >>>>> >>>>> >>>>> >>>> -- >>>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >>>> To make changes to your subscription: >>>> http://www.postgresql.org/mailpref/pgsql-novice >>> >>> > >
SELECT DISTINCT bike.bike_id FROM bike WHERE bike.model='Kona Dew SE FRR' AND booking.booking_date='2011-11-20' JOIN booking ON bike.bike_id=booking.bike_id; returns a syntax error at JOIN SELECT DISTINCT bike.bike_id FROM bike LEFT JOIN booking ON bike.bike_id = booking.bike_id AND booking.booking_date <> '2011-11-20' WHERE bike.model = 'Kona Dew SE FRR'; returns 23 results - two of those results have a booking date of 2011-11-20, so it should return 21 results. Cheers On 7 December 2011 21:22, Ramy Abdel-Azim <ramy.abdel-azim@startdatelabs.com> wrote: > could you post the query you tried? > > > On 12/7/11 4:20 PM, Jude Lucien wrote: >> >> That query doesn't work for me as I can't make seem to add "WHERE >> model='model'" to the query if I use a JOIN. >> >> >> On 7 December 2011 21:16, Ramy Abdel-Azim >> <ramy.abdel-azim@startdatelabs.com> wrote: >>> >>> try it out and let us know. >>> we anxiously await your reply ;-) >>> >>> >>> On 12/7/11 4:15 PM, Jude Lucien wrote: >>>> >>>> I've just realised why it may not be working - bike_id is not the >>>> primary key of booking. booking_id is. >>>> >>>> I was getting results, just not the correct ones. There are 23 bikes >>>> of a certain model, two of which are booked on a certain date. My >>>> query should then return 21 bike_id's. >>>> >>>> Can I even do a join if booking.bike_id is not the primary key? >>>> >>>> >>>> On 7 December 2011 21:02, Ramy Abdel-Azim >>>> <ramy.abdel-azim@startdatelabs.com> wrote: >>>>> >>>>> i'm not sure but i think not being explicit about the join causes psql >>>>> to >>>>> try to join on ID. >>>>> >>>>> What are you getting? an error or an empty result set? >>>>> >>>>> I would think you should do the join explicitly like this: >>>>> >>>>> select bike.bike_id from bike join booking on bike.bike_id = >>>>> booking.bike_id where booking.date != '2011-11-20' >>>>> >>>>> >>>>> On 12/7/11 3:55 PM, Jude Lucien wrote: >>>>>> >>>>>> Sorry for the basic question, I can't seem to figure it out. >>>>>> >>>>>> I have two tables, booking and bike. Both have bike_id as primary >>>>>> key. >>>>>> >>>>>> I am trying to return all bike_id's where bike.model='Kona' AND >>>>>> booking.booking_date DOES NOT EQUAL 'date'; >>>>>> >>>>>> My query is as follows: >>>>>> >>>>>> SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND >>>>>> booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; >>>>>> >>>>>> Any help appreciated! >>>>>> >>>>>> Jude >>>>>> >>>>>> >>>>>> >>>>> -- >>>>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >>>>> To make changes to your subscription: >>>>> http://www.postgresql.org/mailpref/pgsql-novice >>>> >>>> >>>> >> >> > -- "None are more hopelessly enslaved than those who falsely believe they are free" -- Johann Wolfgang von Goethe
your syntax is wrong. look here: http://www.postgresql.org/docs/8.2/static/sql-select.html all joins come before the where clause. On 12/7/11 4:35 PM, Jude Lucien wrote: > SELECT DISTINCT bike.bike_id FROM bike WHERE bike.model='Kona Dew SE > FRR' AND booking.booking_date='2011-11-20' JOIN booking ON > bike.bike_id=booking.bike_id; > > returns a syntax error at JOIN > > SELECT DISTINCT bike.bike_id FROM bike LEFT JOIN booking ON > bike.bike_id = booking.bike_id AND booking.booking_date<> > '2011-11-20' WHERE bike.model = 'Kona Dew SE FRR'; > > returns 23 results - two of those results have a booking date of > 2011-11-20, so it should return 21 results. > > Cheers > > > On 7 December 2011 21:22, Ramy Abdel-Azim > <ramy.abdel-azim@startdatelabs.com> wrote: >> could you post the query you tried? >> >> >> On 12/7/11 4:20 PM, Jude Lucien wrote: >>> That query doesn't work for me as I can't make seem to add "WHERE >>> model='model'" to the query if I use a JOIN. >>> >>> >>> On 7 December 2011 21:16, Ramy Abdel-Azim >>> <ramy.abdel-azim@startdatelabs.com> wrote: >>>> try it out and let us know. >>>> we anxiously await your reply ;-) >>>> >>>> >>>> On 12/7/11 4:15 PM, Jude Lucien wrote: >>>>> I've just realised why it may not be working - bike_id is not the >>>>> primary key of booking. booking_id is. >>>>> >>>>> I was getting results, just not the correct ones. There are 23 bikes >>>>> of a certain model, two of which are booked on a certain date. My >>>>> query should then return 21 bike_id's. >>>>> >>>>> Can I even do a join if booking.bike_id is not the primary key? >>>>> >>>>> >>>>> On 7 December 2011 21:02, Ramy Abdel-Azim >>>>> <ramy.abdel-azim@startdatelabs.com> wrote: >>>>>> i'm not sure but i think not being explicit about the join causes psql >>>>>> to >>>>>> try to join on ID. >>>>>> >>>>>> What are you getting? an error or an empty result set? >>>>>> >>>>>> I would think you should do the join explicitly like this: >>>>>> >>>>>> select bike.bike_id from bike join booking on bike.bike_id = >>>>>> booking.bike_id where booking.date != '2011-11-20' >>>>>> >>>>>> >>>>>> On 12/7/11 3:55 PM, Jude Lucien wrote: >>>>>>> Sorry for the basic question, I can't seem to figure it out. >>>>>>> >>>>>>> I have two tables, booking and bike. Both have bike_id as primary >>>>>>> key. >>>>>>> >>>>>>> I am trying to return all bike_id's where bike.model='Kona' AND >>>>>>> booking.booking_date DOES NOT EQUAL 'date'; >>>>>>> >>>>>>> My query is as follows: >>>>>>> >>>>>>> SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND >>>>>>> booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; >>>>>>> >>>>>>> Any help appreciated! >>>>>>> >>>>>>> Jude >>>>>>> >>>>>>> >>>>>>> >>>>>> -- >>>>>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >>>>>> To make changes to your subscription: >>>>>> http://www.postgresql.org/mailpref/pgsql-novice >>>>> >>>>> >>> > >
Hi Jude,
- Bret
____________________________________________
"Why should I fret in microcosmic bonds
That chafe the spirit, and the mind repress,
When through the clouds gleam beckoning beyonds
Where shining vistas mock man's littleness?"
- H.P. Lovecraft, "Phaeton" (1918)
On 7 December 2011 16:35, Jude Lucien <jlucien@gmail.com> wrote:
-- SELECT DISTINCT bike.bike_id FROM bike WHERE bike.model='Kona Dew SE
FRR' AND booking.booking_date='2011-11-20' JOIN booking ON
bike.bike_id=booking.bike_id;
returns a syntax error at JOIN
JOIN is in the wrong location. Needs to be before the WHERE clause.
SELECT DISTINCT bike.bike_id FROM bike LEFT JOIN booking ON
bike.bike_id = booking.bike_id AND booking.booking_date <>
'2011-11-20' WHERE bike.model = 'Kona Dew SE FRR';
You're comparing the booking_date in the JOIN and you should be doing this in the WHERE clause since that will select only the booking.bike_id's rented on all other dates.
returns 23 results - two of those results have a booking date of
2011-11-20, so it should return 21 results.
Cheers
- Bret
____________________________________________
"Why should I fret in microcosmic bonds
That chafe the spirit, and the mind repress,
When through the clouds gleam beckoning beyonds
Where shining vistas mock man's littleness?"
- H.P. Lovecraft, "Phaeton" (1918)
try this: SELECT DISTINCT bike.bike_id FROM bike JOIN booking ON bike.bike_id=booking.bike_id WHERE bike.model='Kona Dew SEFRR' AND booking.booking_date='2011-11-20' ; On 12/7/11 4:45 PM, Ramy Abdel-Azim wrote: > your syntax is wrong. > look here: > http://www.postgresql.org/docs/8.2/static/sql-select.html > > all joins come before the where clause. > > On 12/7/11 4:35 PM, Jude Lucien wrote: >> SELECT DISTINCT bike.bike_id FROM bike WHERE bike.model='Kona Dew SE >> FRR' AND booking.booking_date='2011-11-20' JOIN booking ON >> bike.bike_id=booking.bike_id; >> >> returns a syntax error at JOIN >> >> SELECT DISTINCT bike.bike_id FROM bike LEFT JOIN booking ON >> bike.bike_id = booking.bike_id AND booking.booking_date<> >> '2011-11-20' WHERE bike.model = 'Kona Dew SE FRR'; >> >> returns 23 results - two of those results have a booking date of >> 2011-11-20, so it should return 21 results. >> >> Cheers >> >> >> On 7 December 2011 21:22, Ramy Abdel-Azim >> <ramy.abdel-azim@startdatelabs.com> wrote: >>> could you post the query you tried? >>> >>> >>> On 12/7/11 4:20 PM, Jude Lucien wrote: >>>> That query doesn't work for me as I can't make seem to add "WHERE >>>> model='model'" to the query if I use a JOIN. >>>> >>>> >>>> On 7 December 2011 21:16, Ramy Abdel-Azim >>>> <ramy.abdel-azim@startdatelabs.com> wrote: >>>>> try it out and let us know. >>>>> we anxiously await your reply ;-) >>>>> >>>>> >>>>> On 12/7/11 4:15 PM, Jude Lucien wrote: >>>>>> I've just realised why it may not be working - bike_id is not the >>>>>> primary key of booking. booking_id is. >>>>>> >>>>>> I was getting results, just not the correct ones. There are 23 >>>>>> bikes >>>>>> of a certain model, two of which are booked on a certain date. My >>>>>> query should then return 21 bike_id's. >>>>>> >>>>>> Can I even do a join if booking.bike_id is not the primary key? >>>>>> >>>>>> >>>>>> On 7 December 2011 21:02, Ramy Abdel-Azim >>>>>> <ramy.abdel-azim@startdatelabs.com> wrote: >>>>>>> i'm not sure but i think not being explicit about the join >>>>>>> causes psql >>>>>>> to >>>>>>> try to join on ID. >>>>>>> >>>>>>> What are you getting? an error or an empty result set? >>>>>>> >>>>>>> I would think you should do the join explicitly like this: >>>>>>> >>>>>>> select bike.bike_id from bike join booking on bike.bike_id = >>>>>>> booking.bike_id where booking.date != '2011-11-20' >>>>>>> >>>>>>> >>>>>>> On 12/7/11 3:55 PM, Jude Lucien wrote: >>>>>>>> Sorry for the basic question, I can't seem to figure it out. >>>>>>>> >>>>>>>> I have two tables, booking and bike. Both have bike_id as primary >>>>>>>> key. >>>>>>>> >>>>>>>> I am trying to return all bike_id's where bike.model='Kona' AND >>>>>>>> booking.booking_date DOES NOT EQUAL 'date'; >>>>>>>> >>>>>>>> My query is as follows: >>>>>>>> >>>>>>>> SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND >>>>>>>> booking.booking_date!='2011-11-20' AND >>>>>>>> bike.bike_id=booking.bike_id; >>>>>>>> >>>>>>>> Any help appreciated! >>>>>>>> >>>>>>>> Jude >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> -- >>>>>>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >>>>>>> To make changes to your subscription: >>>>>>> http://www.postgresql.org/mailpref/pgsql-novice >>>>>> >>>>>> >>>> >> >> >
This is it. There are other instances of bike_id in the booking database that have different booking dates. I can find bike_id's that match my specified booking date, but then I need to run a second query to find the bike_id's that are not booked on that date. Is there any way to join the two queries into one? Thanks for all the help so far. On 7 December 2011 21:53, Bret Fledderjohn <freelancer317@gmail.com> wrote: > Hi Jude, > > On 7 December 2011 16:35, Jude Lucien <jlucien@gmail.com> wrote: >> >> SELECT DISTINCT bike.bike_id FROM bike WHERE bike.model='Kona Dew SE >> FRR' AND booking.booking_date='2011-11-20' JOIN booking ON >> bike.bike_id=booking.bike_id; >> >> returns a syntax error at JOIN > > > JOIN is in the wrong location. Needs to be before the WHERE clause. >> >> >> SELECT DISTINCT bike.bike_id FROM bike LEFT JOIN booking ON >> bike.bike_id = booking.bike_id AND booking.booking_date <> >> '2011-11-20' WHERE bike.model = 'Kona Dew SE FRR'; > > > You're comparing the booking_date in the JOIN and you should be doing this > in the WHERE clause since that will select only the booking.bike_id's rented > on all other dates. >> >> >> returns 23 results - two of those results have a booking date of >> 2011-11-20, so it should return 21 results. >> >> Cheers >> > -- > > - Bret > ____________________________________________ > "Why should I fret in microcosmic bonds > That chafe the spirit, and the mind repress, > When through the clouds gleam beckoning beyonds > Where shining vistas mock man's littleness?" > - H.P. Lovecraft, "Phaeton" (1918) -- "None are more hopelessly enslaved than those who falsely believe they are free" -- Johann Wolfgang von Goethe
On 7 December 2011 16:58, Jude Lucien <jlucien@gmail.com> wrote:
This is it. There are other instances of bike_id in the booking
database that have different booking dates.
I can find bike_id's that match my specified booking date, but then I
need to run a second query to find the bike_id's that are not booked
on that date.
Is there any way to join the two queries into one?
Thanks for all the help so far.
- Bret
____________________________________________
"Why should I fret in microcosmic bonds
That chafe the spirit, and the mind repress,
When through the clouds gleam beckoning beyonds
Where shining vistas mock man's littleness?"
- H.P. Lovecraft, "Phaeton" (1918)
Thanks all, I have my one query all figured out. I was struggling, I appreciate your help! On 8 December 2011 03:44, Bret Fledderjohn <freelancer317@gmail.com> wrote: > > > On 7 December 2011 16:58, Jude Lucien <jlucien@gmail.com> wrote: >> >> This is it. There are other instances of bike_id in the booking >> database that have different booking dates. >> >> I can find bike_id's that match my specified booking date, but then I >> need to run a second query to find the bike_id's that are not booked >> on that date. >> >> Is there any way to join the two queries into one? > > > UNION[ALL] See this > http://www.postgresql.org/docs/9.0/interactive/queries-union.html > >> >> Thanks for all the help so far. > > > - Bret > ____________________________________________ > "Why should I fret in microcosmic bonds > That chafe the spirit, and the mind repress, > When through the clouds gleam beckoning beyonds > Where shining vistas mock man's littleness?" > - H.P. Lovecraft, "Phaeton" (1918) -- "None are more hopelessly enslaved than those who falsely believe they are free" -- Johann Wolfgang von Goethe
Hello all, This time I am moving to something more advanced than assuming bugs that they don't exist. I am creating a stored procedure that accepts 2 inputs and should return many record (by record(s) I mean "set of...."). I have notice however that there are many ways to achieve this and given my 22million rows I need the faster solution. So what my SP should return? 1. A refcursor? 2. A setof Records with record fields declared as OUT in the parameters? 3. A setof records with the record type explicitly declared in my schema? Not sure If I have exhausted all the option. Your advice will be much appreciated. The basic select is happening in a single table. Kind Regards Yiannis