Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Join and limit the result question

  
   Database Help (Home) -> mySQL RSS
Next:  hide sys files from ODBC connection  
Author Message
Thomas Gabrielsen

External


Since: May 18, 2010
Posts: 4



(Msg. 1) Posted: Tue May 18, 2010 2:53 pm
Post subject: Join and limit the result question
Archived from groups: comp>databases>mysql (more info?)
Hi guys!

I have a challenge that I hope you can help me with. I have three
tables and I have exported them with some sample data and uploaded
them here: http://server.arton.no/filesharing/SQL.zip

Here is how the tables looks like:
"zc_artonproject_content". Fields: contentId, projectId, img, name,
ordering
"zc_artonproject_tags" Fields: id, title, inmenu, visible, ordering
"zc_artonproject_tagrelation" Fields: tagId, contentId, projectId

What I want to do is to get "tagId", "contentId", "projectId" from the
"zc_artonproject_tagrelation", and "img" from the
"zc_artonproject_content", and "title" from "zc_artonproject_tags"
selected by a tagId.

So for example let's say that I use '1' as tagId, this is my attempt
so far:

// get the tagId's I want to exclude
SELECT id FROM zc_artonproject_tags WHERE id != 1 AND inmenu = 1 OR
visible = 0";

I load this as an array in PHP and assign this to the variable
$excludedTags and use it in the next query:

SELECT tr.tagId, tr.contentId, tr.projectId, c.img, t.title
FROM zc_artonproject_tagrelation tr, zc_artonproject_content c,
zc_artonproject_tags t"
WHERE tr.contentId = c.contentId
AND tr.tagId = t.id
AND tr.contentId IN (SELECT contentId
FROM zc_artonproject_tagrelation
WHERE tagId = 1
AND tagId NOT IN('". implode( ',',
$excludedTags) ."'))

Now I get all the rows where contentId has tagId 1 except the tagId's
I want to exclude from the result. Then I use PHP to create an array
with nested arrays sorted by tagId containing objects with the data,
and then sort the nested arrays by their length.

The result is pretty close to what I want. But as you can see below
the result have multiple rows with the same projectId and tagId, and I
want only one row per projectId with the same tagId. For example look
at the result below. You can see that in the first array the projectId
"509" is repeated with tagId "1" four times. I want only one row. The
same goes for the second array where projectId "509" is repeated with
tagId "10" four times, and again here I want only one. I could create
a loop in PHP to remove all the duplicates, but I'm sure this is more
appropriate to do this in the mysql query both by readability and
performance. I'm also curious to see how this can be done in mysql
only.

Can you fine people point me in the right direction, or even better
give me an example on how to do this?

Thanks in advance!

Best regards Thomas

PS: I've tried to explain this as good as I can, but please don't
hesitate to ask if you have any questions!

My result so far:

# [array] 0

* [stdClass object] 0
Properties
+ [string] tagId = "1"
+ [string] contentId = "22"
+ [string] projectId = "543"
+ [string] img = "perspektiv2.jpg"
+ [string] title = "kontor"
* [stdClass object] 1
Properties
+ [string] tagId = "1"
+ [string] contentId = "90"
+ [string] projectId = "547"
+ [string] img = "mte7.jpg"
+ [string] title = "kontor"
* [stdClass object] 2
Properties
+ [string] tagId = "1"
+ [string] contentId = "95"
+ [string] projectId = "509"
+ [string] img = "front-side.jpg"
+ [string] title = "kontor"
* [stdClass object] 3
Properties
+ [string] tagId = "1"
+ [string] contentId = "96"
+ [string] projectId = "509"
+ [string] img = "front.jpg"
+ [string] title = "kontor"
* [stdClass object] 4
Properties
+ [string] tagId = "1"
+ [string] contentId = "97"
+ [string] projectId = "509"
+ [string] img = "side.jpg"
+ [string] title = "kontor"
* [stdClass object] 5
Properties
+ [string] tagId = "1"
+ [string] contentId = "99"
+ [string] projectId = "509"
+ [string] img = "fr-web.jpg"
+ [string] title = "kontor"
* [stdClass object] 6
Properties
+ [string] tagId = "1"
+ [string] contentId = "100"
+ [string] projectId = "447"
+ [string] img = "resepsjon2.jpg"
+ [string] title = "kontor"
* [stdClass object] 7
Properties
+ [string] tagId = "1"
+ [string] contentId = "111"
+ [string] projectId = "438"
+ [string] img = "peis5.jpg"
+ [string] title = "kontor"
* [stdClass object] 8
Properties
+ [string] tagId = "1"
+ [string] contentId = "113"
+ [string] projectId = "438"
+ [string] img = "kantine11.jpg"
+ [string] title = "kontor"
* [stdClass object] 9
Properties
+ [string] tagId = "1"
+ [string] contentId = "114"
+ [string] projectId = "438"
+ [string] img = "mte10.jpg"
+ [string] title = "kontor"

# [array] 1

* [stdClass object] 0
Properties
+ [string] tagId = "10"
+ [string] contentId = "22"
+ [string] projectId = "543"
+ [string] img = "perspektiv2.jpg"
+ [string] title = "fasade"
* [stdClass object] 1
Properties
+ [string] tagId = "10"
+ [string] contentId = "95"
+ [string] projectId = "509"
+ [string] img = "front-side.jpg"
+ [string] title = "fasade"
* [stdClass object] 2
Properties
+ [string] tagId = "10"
+ [string] contentId = "96"
+ [string] projectId = "509"
+ [string] img = "front.jpg"
+ [string] title = "fasade"
* [stdClass object] 3
Properties
+ [string] tagId = "10"
+ [string] contentId = "97"
+ [string] projectId = "509"
+ [string] img = "side.jpg"
+ [string] title = "fasade"
* [stdClass object] 4
Properties
+ [string] tagId = "10"
+ [string] contentId = "99"
+ [string] projectId = "509"
+ [string] img = "fr-web.jpg"
+ [string] title = "fasade"

# [array] 2

* [stdClass object] 0
Properties
+ [string] tagId = "11"
+ [string] contentId = "111"
+ [string] projectId = "438"
+ [string] img = "peis5.jpg"
+ [string] title = "kantine"
* [stdClass object] 1
Properties
+ [string] tagId = "11"
+ [string] contentId = "113"
+ [string] projectId = "438"
+ [string] img = "kantine11.jpg"
+ [string] title = "kantine"
* [stdClass object] 2
Properties
+ [string] tagId = "11"
+ [string] contentId = "114"
+ [string] projectId = "438"
+ [string] img = "mte10.jpg"
+ [string] title = "kantine"

# [array] 3

* [stdClass object] 0
Properties
+ [string] tagId = "12"
+ [string] contentId = "90"
+ [string] projectId = "547"
+ [string] img = "mte7.jpg"
+ [string] title = "møterom"
* [stdClass object] 1
Properties
+ [string] tagId = "12"
+ [string] contentId = "114"
+ [string] projectId = "438"
+ [string] img = "mte10.jpg"
+ [string] title = "møterom"

# [array] 4

* [stdClass object] 0
Properties
+ [string] tagId = "4"
+ [string] contentId = "100"
+ [string] projectId = "447"
+ [string] img = "resepsjon2.jpg"
+ [string] title = "resepsjon"

 >> Stay informed about: Join and limit the result question 
Back to top
Login to vote
Captain Paralytic

External


Since: Jan 14, 2008
Posts: 245



(Msg. 2) Posted: Tue May 18, 2010 3:07 pm
Post subject: Re: Join and limit the result question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On May 18, 10:53 pm, Thomas Gabrielsen
wrote:
> Hi guys!
>
> I have a challenge that I hope you can help me with. I have three
> tables and I have exported them with some sample data and uploaded
> them here:http://server.arton.no/filesharing/SQL.zip
>
> Here is how the tables looks like:
> "zc_artonproject_content". Fields:  contentId, projectId, img, name,
> ordering
> "zc_artonproject_tags" Fields: id, title, inmenu, visible, ordering
> "zc_artonproject_tagrelation" Fields:  tagId, contentId, projectId
>
> What I want to do is to get "tagId", "contentId", "projectId" from the
> "zc_artonproject_tagrelation", and "img" from the
> "zc_artonproject_content", and "title" from "zc_artonproject_tags"
> selected by a tagId.
>
> So for example let's say that I use '1' as tagId, this is my attempt
> so far:
>
> // get the tagId's I want to exclude
> SELECT id FROM zc_artonproject_tags WHERE id != 1 AND inmenu = 1 OR
> visible = 0";
>
> I load this as an array in PHP and assign this to the variable
> $excludedTags and use it in the next query:
>
> SELECT tr.tagId, tr.contentId, tr.projectId, c.img, t.title
> FROM zc_artonproject_tagrelation tr, zc_artonproject_content c,
> zc_artonproject_tags t"
> WHERE tr.contentId = c.contentId
> AND tr.tagId = t.id
> AND tr.contentId IN (SELECT contentId
>                       FROM zc_artonproject_tagrelation
>                       WHERE tagId = 1
>                       AND tagId NOT IN('". implode( ',',
> $excludedTags) ."'))
>
> Now I get all the rows where contentId has tagId 1 except the tagId's
> I want to exclude from the result. Then I use PHP to create an array
> with nested arrays sorted by tagId containing objects with the data,
> and then sort the nested arrays by their length.
>
> The result is pretty close to what I want. But as you can see below
> the result have multiple rows with the same projectId and tagId, and I
> want only one row per projectId with the same tagId. For example look
> at the result below. You can see that in the first array the projectId
> "509" is repeated with tagId "1" four times. I want only one row. The
> same goes for the second array where projectId "509" is repeated with
> tagId "10" four times, and again here I want only one. I could create
> a loop in PHP to remove all the duplicates, but I'm sure this is more
> appropriate to do this in the mysql query both by readability and
> performance. I'm also curious to see how this can be done in mysql
> only.
>
> Can you fine people point me in the right direction, or even better
> give me an example on how to do this?
>
> Thanks in advance!
>
> Best regards Thomas
>
> PS: I've tried to explain this as good as I can, but please don't
> hesitate to ask if you have any questions!
>
> My result so far:
>
> #  [array] 0
>
>     * [stdClass object] 0
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "22"
>                 + [string] projectId = "543"
>                 + [string] img = "perspektiv2.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 1
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "90"
>                 + [string] projectId = "547"
>                 + [string] img = "mte7.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 2
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "95"
>                 + [string] projectId = "509"
>                 + [string] img = "front-side.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 3
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "96"
>                 + [string] projectId = "509"
>                 + [string] img = "front.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 4
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "97"
>                 + [string] projectId = "509"
>                 + [string] img = "side.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 5
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "99"
>                 + [string] projectId = "509"
>                 + [string] img = "fr-web.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 6
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "100"
>                 + [string] projectId = "447"
>                 + [string] img = "resepsjon2.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 7
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "111"
>                 + [string] projectId = "438"
>                 + [string] img = "peis5.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 8
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "113"
>                 + [string] projectId = "438"
>                 + [string] img = "kantine11.jpg"
>                 + [string] title = "kontor"
>     * [stdClass object] 9
>           Properties
>                 + [string] tagId = "1"
>                 + [string] contentId = "114"
>                 + [string] projectId = "438"
>                 + [string] img = "mte10.jpg"
>                 + [string] title = "kontor"
>
> # [array] 1
>
>     * [stdClass object] 0
>           Properties
>                 + [string] tagId = "10"
>                 + [string] contentId = "22"
>                 + [string] projectId = "543"
>                 + [string] img = "perspektiv2.jpg"
>                 + [string] title = "fasade"
>     * [stdClass object] 1
>           Properties
>                 + [string] tagId = "10"
>                 + [string] contentId = "95"
>                 + [string] projectId = "509"
>                 + [string] img = "front-side.jpg"
>                 + [string] title = "fasade"
>     * [stdClass object] 2
>           Properties
>                 + [string] tagId = "10"
>                 + [string] contentId = "96"
>                 + [string] projectId = "509"
>                 + [string] img = "front.jpg"
>                 + [string] title = "fasade"
>     * [stdClass object] 3
>           Properties
>                 + [string] tagId = "10"
>                 + [string] contentId = "97"
>                 + [string] projectId = "509"
>                 + [string] img = "side.jpg"
>                 + [string] title = "fasade"
>     * [stdClass object] 4
>           Properties
>                 + [string] tagId = "10"
>                 + [string] contentId = "99"
>                 + [string] projectId = "509"
>                 + [string] img = "fr-web.jpg"
>                 + [string] title = "fasade"
>
> # [array] 2
>
>     * [stdClass object] 0
>           Properties
>                 + [string] tagId = "11"
>                 + [string] contentId = "111"
>                 + [string] projectId = "438"
>                 + [string] img = "peis5.jpg"
>                 + [string] title = "kantine"
>     * [stdClass object] 1
>           Properties
>                 + [string] tagId = "11"
>                 + [string] contentId = "113"
>                 + [string] projectId = "438"
>                 + [string] img = "kantine11.jpg"
>                 + [string] title = "kantine"
>     * [stdClass object] 2
>           Properties
>                 + [string] tagId = "11"
>                 + [string] contentId = "114"
>                 + [string] projectId = "438"
>                 + [string] img = "mte10.jpg"
>                 + [string] title = "kantine"
>
> # [array] 3
>
>     * [stdClass object] 0
>           Properties
>                 + [string] tagId = "12"
>                 + [string] contentId = "90"
>                 + [string] projectId = "547"
>                 + [string] img = "mte7.jpg"
>                 + [string] title = "møterom"
>     * [stdClass object] 1
>           Properties
>                 + [string] tagId = "12"
>                 + [string] contentId = "114"
>                 + [string] projectId = "438"
>                 + [string] img = "mte10.jpg"
>                 + [string] title = "møterom"
>
> # [array] 4
>
>     * [stdClass object] 0
>           Properties
>                 + [string] tagId = "4"
>                 + [string] contentId = "100"
>                 + [string] projectId = "447"
>                 + [string] img = "resepsjon2.jpg"
>                 + [string] title = "resepsjon"

Well lots of comments to make here, but first a question:
You say: "You can see that in the first array the projectId "509" is
repeated with tagId "1" four times. I want only one row."
But which row? Each of them has different values of img. If it doesn't
matter which value of img you have, why is it there at all?
If it does matter, what is the criteria for which one you want?

Now some comments:
1) Don't use implicit comma joins, use explicit JOINs
2) Don't carry values from one query to another using php, just add
another JOIN
3) Avoid sub-SELECTS with the IN and NOT IN statements, use a LEFT
JOIN and an IS NULL on the IDs that are to be excluded

I will help you with these, but I really need the answer to the
question first.

 >> Stay informed about: Join and limit the result question 
Back to top
Login to vote
Thomas Gabrielsen

External


Since: May 18, 2010
Posts: 4



(Msg. 3) Posted: Wed May 19, 2010 1:13 am
Post subject: Re: Join and limit the result question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On 19 Mai, 00:07, Captain Paralytic wrote:
> On May 18, 10:53 pm, Thomas Gabrielsen
> wrote:
>
> > Hi guys!
>
> > I have a challenge that I hope you can help me with. I have three
> > tables and I have exported them with some sample data and uploaded
> > them here:http://server.arton.no/filesharing/SQL.zip
>
> > Here is how the tables looks like:
> > "zc_artonproject_content". Fields:  contentId, projectId, img, name,
> > ordering
> > "zc_artonproject_tags" Fields: id, title, inmenu, visible, ordering
> > "zc_artonproject_tagrelation" Fields:  tagId, contentId, projectId
>
> > What I want to do is to get "tagId", "contentId", "projectId" from the
> > "zc_artonproject_tagrelation", and "img" from the
> > "zc_artonproject_content", and "title" from "zc_artonproject_tags"
> > selected by a tagId.
>
> > So for example let's say that I use '1' as tagId, this is my attempt
> > so far:
>
> > // get the tagId's I want to exclude
> > SELECT id FROM zc_artonproject_tags WHERE id != 1 AND inmenu = 1 OR
> > visible = 0";
>
> > I load this as an array in PHP and assign this to the variable
> > $excludedTags and use it in the next query:
>
> > SELECT tr.tagId, tr.contentId, tr.projectId, c.img, t.title
> > FROM zc_artonproject_tagrelation tr, zc_artonproject_content c,
> > zc_artonproject_tags t"
> > WHERE tr.contentId = c.contentId
> > AND tr.tagId = t.id
> > AND tr.contentId IN (SELECT contentId
> >                       FROM zc_artonproject_tagrelation
> >                       WHERE tagId = 1
> >                       AND tagId NOT IN('". implode( ',',
> > $excludedTags) ."'))
>
> > Now I get all the rows where contentId has tagId 1 except the tagId's
> > I want to exclude from the result. Then I use PHP to create an array
> > with nested arrays sorted by tagId containing objects with the data,
> > and then sort the nested arrays by their length.
>
> > The result is pretty close to what I want. But as you can see below
> > the result have multiple rows with the same projectId and tagId, and I
> > want only one row per projectId with the same tagId. For example look
> > at the result below. You can see that in the first array the projectId
> > "509" is repeated with tagId "1" four times. I want only one row. The
> > same goes for the second array where projectId "509" is repeated with
> > tagId "10" four times, and again here I want only one. I could create
> > a loop in PHP to remove all the duplicates, but I'm sure this is more
> > appropriate to do this in the mysql query both by readability and
> > performance. I'm also curious to see how this can be done in mysql
> > only.
>
> > Can you fine people point me in the right direction, or even better
> > give me an example on how to do this?
>
> > Thanks in advance!
>
> > Best regards Thomas
>
> > PS: I've tried to explain this as good as I can, but please don't
> > hesitate to ask if you have any questions!
>
> > My result so far:
>
> > #  [array] 0
>
> >     * [stdClass object] 0
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "22"
> >                 + [string] projectId = "543"
> >                 + [string] img = "perspektiv2.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 1
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "90"
> >                 + [string] projectId = "547"
> >                 + [string] img = "mte7.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 2
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "95"
> >                 + [string] projectId = "509"
> >                 + [string] img = "front-side.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 3
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "96"
> >                 + [string] projectId = "509"
> >                 + [string] img = "front.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 4
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "97"
> >                 + [string] projectId = "509"
> >                 + [string] img = "side.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 5
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "99"
> >                 + [string] projectId = "509"
> >                 + [string] img = "fr-web.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 6
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "100"
> >                 + [string] projectId = "447"
> >                 + [string] img = "resepsjon2.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 7
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "111"
> >                 + [string] projectId = "438"
> >                 + [string] img = "peis5.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 8
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "113"
> >                 + [string] projectId = "438"
> >                 + [string] img = "kantine11.jpg"
> >                 + [string] title = "kontor"
> >     * [stdClass object] 9
> >           Properties
> >                 + [string] tagId = "1"
> >                 + [string] contentId = "114"
> >                 + [string] projectId = "438"
> >                 + [string] img = "mte10.jpg"
> >                 + [string] title = "kontor"
>
> > # [array] 1
>
> >     * [stdClass object] 0
> >           Properties
> >                 + [string] tagId = "10"
> >                 + [string] contentId = "22"
> >                 + [string] projectId = "543"
> >                 + [string] img = "perspektiv2.jpg"
> >                 + [string] title = "fasade"
> >     * [stdClass object] 1
> >           Properties
> >                 + [string] tagId = "10"
> >                 + [string] contentId = "95"
> >                 + [string] projectId = "509"
> >                 + [string] img = "front-side.jpg"
> >                 + [string] title = "fasade"
> >     * [stdClass object] 2
> >           Properties
> >                 + [string] tagId = "10"
> >                 + [string] contentId = "96"
> >                 + [string] projectId = "509"
> >                 + [string] img = "front.jpg"
> >                 + [string] title = "fasade"
> >     * [stdClass object] 3
> >           Properties
> >                 + [string] tagId = "10"
> >                 + [string] contentId = "97"
> >                 + [string] projectId = "509"
> >                 + [string] img = "side.jpg"
> >                 + [string] title = "fasade"
> >     * [stdClass object] 4
> >           Properties
> >                 + [string] tagId = "10"
> >                 + [string] contentId = "99"
> >                 + [string] projectId = "509"
> >                 + [string] img = "fr-web.jpg"
> >                 + [string] title = "fasade"
>
> > # [array] 2
>
> >     * [stdClass object] 0
> >           Properties
> >                 + [string] tagId = "11"
> >                 + [string] contentId = "111"
> >                 + [string] projectId = "438"
> >                 + [string] img = "peis5.jpg"
> >                 + [string] title = "kantine"
> >     * [stdClass object] 1
> >           Properties
> >                 + [string] tagId = "11"
> >                 + [string] contentId = "113"
> >                 + [string] projectId = "438"
> >                 + [string] img = "kantine11.jpg"
> >                 + [string] title = "kantine"
> >     * [stdClass object] 2
> >           Properties
> >                 + [string] tagId = "11"
> >                 + [string] contentId = "114"
> >                 + [string] projectId = "438"
> >                 + [string] img = "mte10.jpg"
> >                 + [string] title = "kantine"
>
> > # [array] 3
>
> >     * [stdClass object] 0
> >           Properties
> >                 + [string] tagId = "12"
> >                 + [string] contentId = "90"
> >                 + [string] projectId = "547"
> >                 + [string] img = "mte7.jpg"
> >                 + [string] title = "møterom"
> >     * [stdClass object] 1
> >           Properties
> >                 + [string] tagId = "12"
> >                 + [string] contentId = "114"
> >                 + [string] projectId = "438"
> >                 + [string] img = "mte10.jpg"
> >                 + [string] title = "møterom"
>
> > # [array] 4
>
> >     * [stdClass object] 0
> >           Properties
> >                 + [string] tagId = "4"
> >                 + [string] contentId = "100"
> >                 + [string] projectId = "447"
> >                 + [string] img = "resepsjon2.jpg"
> >                 + [string] title = "resepsjon"
>
> Well lots of comments to make here, but first a question:
> You say: "You can see that in the first array the projectId "509" is
> repeated with tagId "1" four times. I want only one row."
> But which row? Each of them has different values of img. If it doesn't
> matter which value of img you have, why is it there at all?
> If it does matter, what is the criteria for which one you want?
>
> Now some comments:
> 1) Don't use implicit comma joins, use explicit JOINs
> 2) Don't carry values from one query to another using php, just add
> another JOIN
> 3) Avoid sub-SELECTS with the IN and NOT IN statements, use a LEFT
> JOIN and an IS NULL on the IDs that are to be excluded
>
> I will help you with these, but I really need the answer to the
> question first.

Hi Captain!

I would prefer to use the "ordering" field in the
"zc_artonproject_content" table to decide which row to select, and use
the lowest number in that ordering field as the row to select. The
"ordering" field in the "zc_artonproject_content" table is based in
the "projectId". The reason I didn't wrote this in the first post is
because it isn't that important, and because I had no clue how to do
it. I was looking for a solution like (in pseudo code) "group
projectId where tagId is the same" ... Wink

Now to your comments:
1) Can you give me a short explanation why you prefer to use explicit
JOINs over comma seperated JOINS?
2) I did use the extra query because I didn't know how to do it
otherwise. It was a way to break down the query to make it more
understandable.
3) I've tried to use LEFT JOINs instead of subqueries, but I didn't
get it right. At my knowledge level of SQL I feel that is more
readable to use subqueries. But I try to dig deeper into using LEFT
JOINs instead, and use it more and more often, but in complex queries
I tend it mess things up and loose context when using LEFT JOINS.

I'm really thankful for that you want to help me with this!

Best,
Thomas
 >> Stay informed about: Join and limit the result question 
Back to top
Login to vote
Captain Paralytic

External


Since: Jan 14, 2008
Posts: 245



(Msg. 4) Posted: Wed May 19, 2010 1:27 am
Post subject: Re: Join and limit the result question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On 19 May, 09:13, Thomas Gabrielsen wrote:
> On 19 Mai, 00:07, Captain Paralytic wrote:
> > Well lots of comments to make here, but first a question:
> > You say: "You can see that in the first array the projectId "509" is
> > repeated with tagId "1" four times. I want only one row."
> > But which row? Each of them has different values of img. If it doesn't
> > matter which value of img you have, why is it there at all?
> > If it does matter, what is the criteria for which one you want?
>
> > Now some comments:
> > 1) Don't use implicit comma joins, use explicit JOINs
> > 2) Don't carry values from one query to another using php, just add
> > another JOIN
> > 3) Avoid sub-SELECTS with the IN and NOT IN statements, use a LEFT
> > JOIN and an IS NULL on the IDs that are to be excluded
>
> > I will help you with these, but I really need the answer to the
> > question first.
>
> Hi Captain!
>
> I would prefer to use the "ordering" field in the
> "zc_artonproject_content" table to decide which row to select, and use
> the lowest number in that ordering field as the row to select. The
> "ordering" field in the "zc_artonproject_content" table is based in
> the "projectId". The reason I didn't wrote this in the first post is
> because it isn't that important, and because I had no clue how to do
> it. I was looking for a solution like (in pseudo code) "group
> projectId where tagId is the same" ... Wink
In that case we need to incorporate the "Strawberry Query" as it has
become known:
http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

> Now to your comments:
> 1) Can you give me a short explanation why you prefer to use explicit
> JOINs over comma seperated JOINS?
1) It separates relationship (ON) conditions from selection criteria
(WHERE)
2) Comma joins have a different precedence value from explicit JOINs.
This can cause problems when say a LEFT JOIN is later added.

> 2) I did use the extra query because I didn't know how to do it
> otherwise. It was a way to break down the query to make it more
> understandable.
> 3) I've tried to use LEFT JOINs instead of subqueries, but I didn't
> get it right. At my knowledge level of SQL I feel that is more
> readable to use subqueries. But I try to dig deeper into using LEFT
> JOINs instead, and use it more and more often, but in complex queries
> I tend it mess things up and loose context when using LEFT JOINS.
>
> I'm really thankful for that you want to help me with this!
>
> Best,
> Thomas
 >> Stay informed about: Join and limit the result question 
Back to top
Login to vote
Thomas Gabrielsen

External


Since: May 18, 2010
Posts: 4



(Msg. 5) Posted: Wed May 19, 2010 4:14 am
Post subject: Re: Join and limit the result question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On 19 Mai, 10:27, Captain Paralytic wrote:
> On 19 May, 09:13, Thomas Gabrielsen wrote:
>
> > On 19 Mai, 00:07, Captain Paralytic wrote:
> > > Well lots of comments to make here, but first a question:
> > > You say: "You can see that in the first array the projectId "509" is
> > > repeated with tagId "1" four times. I want only one row."
> > > But which row? Each of them has different values of img. If it doesn't
> > > matter which value of img you have, why is it there at all?
> > > If it does matter, what is the criteria for which one you want?
>
> > > Now some comments:
> > > 1) Don't use implicit comma joins, use explicit JOINs
> > > 2) Don't carry values from one query to another using php, just add
> > > another JOIN
> > > 3) Avoid sub-SELECTS with the IN and NOT IN statements, use a LEFT
> > > JOIN and an IS NULL on the IDs that are to be excluded
>
> > > I will help you with these, but I really need the answer to the
> > > question first.
>
> > Hi Captain!
>
> > I would prefer to use the "ordering" field in the
> > "zc_artonproject_content" table to decide which row to select, and use
> > the lowest number in that ordering field as the row to select. The
> > "ordering" field in the "zc_artonproject_content" table is based in
> > the "projectId". The reason I didn't wrote this in the first post is
> > because it isn't that important, and because I had no clue how to do
> > it. I was looking for a solution like (in pseudo code) "group
> > projectId where tagId is the same" ... Wink
>
> In that case we need to incorporate the "Strawberry Query" as it has
> become known:http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-r...
>
> > Now to your comments:
> > 1) Can you give me a short explanation why you prefer to use explicit
> > JOINs over comma seperated JOINS?
>
> 1) It separates relationship (ON) conditions from selection criteria
> (WHERE)
> 2) Comma joins have a different precedence value from explicit JOINs.
> This can cause problems when say a LEFT JOIN is later added.
>
> > 2) I did use the extra query because I didn't know how to do it
> > otherwise. It was a way to break down the query to make it more
> > understandable.
> > 3) I've tried to use LEFT JOINs instead of subqueries, but I didn't
> > get it right. At my knowledge level of SQL I feel that is more
> > readable to use subqueries. But I try to dig deeper into using LEFT
> > JOINs instead, and use it more and more often, but in complex queries
> > I tend it mess things up and loose context when using LEFT JOINS.
>
> > I'm really thankful for that you want to help me with this!
>
> > Best,
> > Thomas

Thanks for the link! I've already tried that actally. I remembered it
from an earlier discussion we have had, but I didn't get the result I
wanted or it failed totally with an error. It was that messed up so I
just deleted it and can't paste my attempt. I definitively believe it
is because of my lack of sql skills, not because it's not the right
solution. I will try it once again now.

Thomas
 >> Stay informed about: Join and limit the result question 
Back to top
Login to vote
Captain Paralytic

External


Since: Jan 14, 2008
Posts: 245



(Msg. 6) Posted: Wed May 19, 2010 5:29 am
Post subject: Re: Join and limit the result question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On 19 May, 12:14, Thomas Gabrielsen wrote:
> On 19 Mai, 10:27, Captain Paralytic wrote:
>
>
>
>
>
> > On 19 May, 09:13, Thomas Gabrielsen wrote:
>
> > > On 19 Mai, 00:07, Captain Paralytic wrote:
> > > > Well lots of comments to make here, but first a question:
> > > > You say: "You can see that in the first array the projectId "509" is
> > > > repeated with tagId "1" four times. I want only one row."
> > > > But which row? Each of them has different values of img. If it doesn't
> > > > matter which value of img you have, why is it there at all?
> > > > If it does matter, what is the criteria for which one you want?
>
> > > > Now some comments:
> > > > 1) Don't use implicit comma joins, use explicit JOINs
> > > > 2) Don't carry values from one query to another using php, just add
> > > > another JOIN
> > > > 3) Avoid sub-SELECTS with the IN and NOT IN statements, use a LEFT
> > > > JOIN and an IS NULL on the IDs that are to be excluded
>
> > > > I will help you with these, but I really need the answer to the
> > > > question first.
>
> > > Hi Captain!
>
> > > I would prefer to use the "ordering" field in the
> > > "zc_artonproject_content" table to decide which row to select, and use
> > > the lowest number in that ordering field as the row to select. The
> > > "ordering" field in the "zc_artonproject_content" table is based in
> > > the "projectId". The reason I didn't wrote this in the first post is
> > > because it isn't that important, and because I had no clue how to do
> > > it. I was looking for a solution like (in pseudo code) "group
> > > projectId where tagId is the same" ... Wink
>
> > In that case we need to incorporate the "Strawberry Query" as it has
> > become known:http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-r...
>
> > > Now to your comments:
> > > 1) Can you give me a short explanation why you prefer to use explicit
> > > JOINs over comma seperated JOINS?
>
> > 1) It separates relationship (ON) conditions from selection criteria
> > (WHERE)
> > 2) Comma joins have a different precedence value from explicit JOINs.
> > This can cause problems when say a LEFT JOIN is later added.
>
> > > 2) I did use the extra query because I didn't know how to do it
> > > otherwise. It was a way to break down the query to make it more
> > > understandable.
> > > 3) I've tried to use LEFT JOINs instead of subqueries, but I didn't
> > > get it right. At my knowledge level of SQL I feel that is more
> > > readable to use subqueries. But I try to dig deeper into using LEFT
> > > JOINs instead, and use it more and more often, but in complex queries
> > > I tend it mess things up and loose context when using LEFT JOINS.
>
> > > I'm really thankful for that you want to help me with this!
>
> > > Best,
> > > Thomas
>
> Thanks for the link! I've already tried that actally. I remembered it
> from an earlier discussion we have had, but I didn't get the result I
> wanted or it failed totally with an error. It was that messed up so I
> just deleted it and can't paste my attempt. I definitively believe it
> is because of my lack of sql skills, not because it's not the right
> solution. I will try it once again now.
>
> Thomas

Later on I shall load your zip file and construct the query step by
step.
It will be a useful learning tool for you.
 >> Stay informed about: Join and limit the result question 
Back to top
Login to vote
Thomas Gabrielsen

External


Since: May 18, 2010
Posts: 4



(Msg. 7) Posted: Wed May 19, 2010 7:47 am
Post subject: Re: Join and limit the result question [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On 19 Mai, 14:29, Captain Paralytic wrote:
> On 19 May, 12:14, Thomas Gabrielsen wrote:
>
>
>
> > On 19 Mai, 10:27, Captain Paralytic wrote:
>
> > > On 19 May, 09:13, Thomas Gabrielsen wrote:
>
> > > > On 19 Mai, 00:07, Captain Paralytic wrote:
> > > > > Well lots of comments to make here, but first a question:
> > > > > You say: "You can see that in the first array the projectId "509" is
> > > > > repeated with tagId "1" four times. I want only one row."
> > > > > But which row? Each of them has different values of img. If it doesn't
> > > > > matter which value of img you have, why is it there at all?
> > > > > If it does matter, what is the criteria for which one you want?
>
> > > > > Now some comments:
> > > > > 1) Don't use implicit comma joins, use explicit JOINs
> > > > > 2) Don't carry values from one query to another using php, just add
> > > > > another JOIN
> > > > > 3) Avoid sub-SELECTS with the IN and NOT IN statements, use a LEFT
> > > > > JOIN and an IS NULL on the IDs that are to be excluded
>
> > > > > I will help you with these, but I really need the answer to the
> > > > > question first.
>
> > > > Hi Captain!
>
> > > > I would prefer to use the "ordering" field in the
> > > > "zc_artonproject_content" table to decide which row to select, and use
> > > > the lowest number in that ordering field as the row to select. The
> > > > "ordering" field in the "zc_artonproject_content" table is based in
> > > > the "projectId". The reason I didn't wrote this in the first post is
> > > > because it isn't that important, and because I had no clue how to do
> > > > it. I was looking for a solution like (in pseudo code) "group
> > > > projectId where tagId is the same" ... Wink
>
> > > In that case we need to incorporate the "Strawberry Query" as it has
> > > become known:http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-r...
>
> > > > Now to your comments:
> > > > 1) Can you give me a short explanation why you prefer to use explicit
> > > > JOINs over comma seperated JOINS?
>
> > > 1) It separates relationship (ON) conditions from selection criteria
> > > (WHERE)
> > > 2) Comma joins have a different precedence value from explicit JOINs.
> > > This can cause problems when say a LEFT JOIN is later added.
>
> > > > 2) I did use the extra query because I didn't know how to do it
> > > > otherwise. It was a way to break down the query to make it more
> > > > understandable.
> > > > 3) I've tried to use LEFT JOINs instead of subqueries, but I didn't
> > > > get it right. At my knowledge level of SQL I feel that is more
> > > > readable to use subqueries. But I try to dig deeper into using LEFT
> > > > JOINs instead, and use it more and more often, but in complex queries
> > > > I tend it mess things up and loose context when using LEFT JOINS.
>
> > > > I'm really thankful for that you want to help me with this!
>
> > > > Best,
> > > > Thomas
>
> > Thanks for the link! I've already tried that actally. I remembered it
> > from an earlier discussion we have had, but I didn't get the result I
> > wanted or it failed totally with an error. It was that messed up so I
> > just deleted it and can't paste my attempt. I definitively believe it
> > is because of my lack of sql skills, not because it's not the right
> > solution. I will try it once again now.
>
> > Thomas
>
> Later on I shall load your zip file and construct the query step by
> step.
> It will be a useful learning tool for you.

Indeed! And I really appriciate that you are taking your time to help
me.

My first problem is that I sort out in which order I should start the
LEFT JOINs. I've tried several approaches but the most succesful so
far is this:

SELECT tr1.tagId, tr1.contentId, tr1.projectId
FROM zc_artonproject_tagrelation tr1
LEFT JOIN zc_artonproject_tags t1 ON tr1.tagId = t1.id
WHERE t1.inmenu = 0 AND t1.visible = 1 OR t1.id = 1

Then I get rows that has tagId 1 or the tags that has inmenu = 0 and
visible = 1. But this isn't correct because I want all the contentIds
that has tagId 1 (or another tagId I add to the query), and all the
other contentIds that has tagId 1 or a tagId this has inmenu = 0 and
visible = 1 with their corresponding tagId pluss the tagId I'm using
in the query. For this example I use 1, but it can be any tagId. Then
I need to get the title for the tagId from the ..._tags table, img
from the ..._content table, and finaly I need title from the
zc_artonprojects. I have updated the zip file with the
zc_artonprojects table and uploaded it (http://server.arton.no/
filesharing/SQL.zip). Then I don't want more than one row where
projectId and tagId is the same.
I repeat myself a bit here but I want to be sure that I made myself
clear and it helps me think.
 >> Stay informed about: Join and limit the result question 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Question about UPDATE and LEFT JOIN - Hi, I hope this is the right place to post, but, I am having a problem with an UPDATE command and a LEFT JOIN, I am using something like: UPDATE table_a LEFT JOIN table_b ON table_a.field1=table_b.field1 SET table_b.field6='1' WHERE table_a.field2='1'; ...

Mysql InnoDB PHP result problem - i have a strange problem : MySQL 4.0.21 innodb and php 4.3.9-1 all tables are in innodb type. For a simple select statement sometimes i get result and sometimes nothing (i.e mysql_num_rows()>0 or mysql_num_rows()==0). It's a random behaviour. If my t...

2 x SUM(IF.. and 2x LEFT JOIN - Hi, Suppose I have a query like: SELECT products.name AS product, SUM(IF(stock.invoice=0,1,0)) AS in_stock, SUM(IF(shopcart.status=1,1,0)) AS reserved FROM products LEFT JOIN stock ON products.id=stock.product_id LEFT JOIN shopcart ON..

Best way to issue hundreds of inserts/updates??? - Using mysql 4.0.23- What is the best way to execute several (hundreds of) inserts and updates? Rather than issuing tons of individual inserts and updates, can I send the strings to a text file and then have mysql do them all?? IE : query.txt insert..

MySQL freezes, brings XP machine to a grinding halt - I've been using MySQL for a while for fairly light database development on my XP machine. Currently, I am just starting a new project and have experienced some big problems with MySQL today both on my office machine and at home where running a particular...
   Database Help (Home) -> mySQL All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]