Connect/ Join Query Multiple Dbs Multiple Servers
Struggling to connect simultaneously to remotehost@my_ip_address
AND localhost@my_field_db FROM my_field_tablet_pc to affect
a custom sycnchronization that SQLyog will not allow.
Both remotehost and localhost are built on WAMP stacks.
View Complete Forum Thread with Replies
Related Forum Messages:
Multiple Join Query
I have a database with quite a lot of tables all with foreign keys of each other. One of the tables has a one to many relationship (it being the one) with two tables, but it only uses one of them for any one record. Which one is used depends on a field in the main table. This essentially defines three different types of the same item, which has mostly the same fields, but two of them also require additional, multiple record (hence the one to many) information. So the table has a field 'type' which takes the value 1 (in which case no other table is referenced), 2, (in which case the first other table is used) and 3 (in which the other table is used). What I would ideally like to do is have one SQL query which returns information (from the main table) about any record which applies to the day (as it is part of a calendar system) specified in the query. The problem is that to decide if the record is relivent, the query must look through at most two of the three tables. I doubt that there is a way to specify in an SQL query to join to a table depending on the field of the first, and to join all three and perform the query could produce unexpected results if there were for any reason records in the third which referred to the first table. I could do this with several queries, but it would probably be more efficient to do it by one query. Would I be able to do an outer join on this?
View Replies !
Replication With Multiple Servers In Mysql ?
I want to establish replication between multiple (currently three) servers. Updates/inserts/deletes may happen on all of them. The idea I came up with was to do a "circle"-replication. The problem with this is, that if one server fails the rest may not get updates (until the server is back, obviously). Does anybody have an idea how to establish such a replication in a fail-save manner ? Code:
View Replies !
Replication With Multiple Servers In Mysql
I want to establish replication between multiple (currently three) servers. Updates/inserts/deletes may happen on all of them. The idea I came up with was to do a "circle"-replication. The problem with this is, that if one server fails the rest may not get updates (until the server is back, obviously).Does anybody have an idea how to establish such a replication in a fail-save manner?
View Replies !
Installing Multiple MySQL Servers
If you need multiple MySQL servers running, what's the difference between installing MySQL once and running multiple servers (by issuing multiple mysqld commands with their own options), versus actually installing two or more DIFFERENT instances of the same version of MySQL? What is typically done? What are the advantages/disadvantages of either method?
View Replies !
Moving Multiple Databases From Different Servers To One Db Server
I'm trying to move three MySQL databases, currently residing on two different servers, on a third dedicated database server: Old Server 1: db1a, db1b Old Server 2: db2a New Server 3: <move db1a, db1b, db3a here> All three databases are quite large, 8+ gigs in size, so I was suggested to copy over the data directories, containing these databases, to /var/lib/mysql on the New Server 3. I can foresee potential conflicts due to files and directories with same names on both Old Server 1 and Old Server 2 (specifically, ib_logfile0, ib_logfile1, /mysql/*, etc.). Are there any MySQL tools available that would allow me to either fix the databases afterwards, or consolidate the overlapping data? Maybe a better way to achieve this?
View Replies !
Multiple User Connect To Mysql
want to develop a website using php and mysql. concept of the site is information storage. would like to know if a user is currently filling up a form and another user logon to the site and wish to fill up some information. will the database will be locked? because someone is already connected to the database?
View Replies !
LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want. The tables are: table services service_id service_name table services_provided service_id service_date (date field) cust_id service_quantity I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time The query: SELECT service_date, service_name, service_quantity FROM services LEFT JOIN services_provided ON services_provided.service_id = services.service_id WHERE cust_id = $cust_id AND MONTH(service_date) = 10 AND YEAR(service_date) = 2007 GROUP BY service_id ORDER BY service_id (Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.) I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table. But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results. Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.
View Replies !
Multiple Row Join?
I have two tables - one called parent, the other called group. table group is made up of ID,parentID,groupHash and groupName. table parent is made up of ID,title,group1Hash,group2Hash,group3Hash (and other columns) Each of the group hashes in table parent correspond to a group hash in table group (ie parent.group1Hash exists in/as group.groupHash and parent.group2Hash also exists (on another row) in/as group.groupHash). This means table parent can have up to three groupHash's noted in a single row and each of these groupHash's will have its name in a corresponding row in table group (using groupHash as key). Can I perform a select whereby I read a row from parent and use its group1Hash, group2Hash and group3Hash to retrieve their corresponding names from table group (all in one select statement)? Or must I perform three or four individual selects to get the desired values from the two tables.
View Replies !
Multiple Join
I have a query which works, but I would like to know if I can get it to work without having to use 'group by'? If I do not use the 'group by' I get multiple results of the same thing. Code: select C.call_id, C.extn_no, dest, G.group_name from call_data as C, group_user as G left join user_grp as U on G.group_id = U.group_id left join grp_user on C.extn_no = G.extn_no where C.direction = 'Out' group by C.call_id
View Replies !
Confused By Multiple JOIN
My query is giving me a syntax error. How do I do this? Code: SELECT t.id, t.title, t.name, t.company, t.address, t.country, t.postcode, t.telephone, t.email, tm.msg, ts.site FROM trials AS t LEFT JOIN trials_sites AS ts ON (ts.trial_id = t.id) LEFT JOIN trials_msg AS tm ON (tm.trial_id = t.id) LEFT JOIN trials_times AS tt ON (t.id = tt.trial_id) LEFT JOIN trial_days AS td ON (tt.day_id = td.id) WHERE td.day = 'Monday' ORDER BY t.id DESC I definitely want a whole row from `trials. But each row may have a row associated with it from each of the joined tables. In the case of the `trial_times` table, there may be more than 1 associated row - or none. Is it possible to execute a query like this?
View Replies !
JOIN Across Multiple Keys In The Same Row
I have a table where each row has multiple foreign keys. What I'd like to do is a JOIN where each row of the returned resultant table contains all the data from each table that each row points to. For example, say there are 2 tables, MainTable and SecondaryTable. MainTable has 2 columns, each of which use a foreign key to point to records in the SecondaryTable like so: ......
View Replies !
Multiple Inner Join Within The Same Table
I want to create a new table from an image table where the columns would be the image_path where image_type =1 and image_type=2 for images with the same slide_id. I used the following query with an error: select slide_id, image_path as thumb_path from image as thumb where thumb.image_type=1 inner join image.image_path on image.slide_id=thumb.slide_id where image.image_type=2; Below is the image table: image image_idslide_idimage_typeimage_path 111/Image/full.jpg 212/Image/thumb.jpg 321/Image/full.jpg 422/Image/thumb.jpg 531/Image/full.jpg 632/Image/thumb.jpg 741/Image/full.jpg 842/Image/thumb.jpg
View Replies !
Multiple Tables With Join
$queryDelete = " DELETE FROM table1,table2 WHERE table1.id=".$_GET['id']." AND table2.id=".$_GET['id']; Looks ok, but doesnt work.....$_GET['id'] is read correctly and available in all tables.
View Replies !
What Join To Use With Multiple Tables?
I'm assuming I need some sort of join to accomplish this but I've yet to find something that I have been able to understand. I have 3 tables - Items, Photos and a table that links the two that I've elegantly named ItemsPhotos Items - ItemID - ItemName - ItemEtc Photos - PhotoID - PhotoFilename - PhotoEtc ItemsPhotos - ItemPhotoID - ItemID - PhotoID Each Item has a unique ID, as does each Photo. The ItemsPhotos keeps track of their relationship. I want to be able to select all of the items and only one of the the available photos listed for that item to be returned to an Array for use like the following; foreach(#){ $databaseOutput[#]['ItemName']; $databaseOutput[#]['PhotoFilename']; } If anoyone knows how I can accomplish this I would be very grateful for a solution. Sincere appologies if there is an obvious answer or if I have mis-posted - I've been trying to get this (among other things) to work for hours now and I'm desperate for a solution.
View Replies !
Multiple Table Join
I am using MYSQL version 3.23.58-16.FC2.1 which has caused me issues in the past. However everything is working so its the old addage if it aint broke...dont fix it. anyway heres the situation, I have three tables clients that have multiple properties and those multiple properties have multiple payments against them. I am trying to list a monthly statement per client, per property, of payments to look something like this :-
View Replies !
Multiple JOIN Problem
I have tables: gallery_album, gallery_photo, node. On node, the important columns are: type (type must be "gallery"), nid (integer node id), uid - node owner id. On gallery_photo, the important colums are: nid (relates photo to node entry), album_id - relates photos to gallery_album entries. On gallery_album, the important colums are: id (must match album_id in gallery_photo), title, must not be empty (check for <> "" does the trick), owner_id For every node entry of type "gallery" there exists exactly one entry in gallery_photos - one to one relationship. For every gallery_album entry there exists any number of gallery_photo and node entries - one to many relationship. I know the user id, I need to return albums belonging to that user together with photo count in that album and newest photo from that album (specified by nid and node.created timestamp). If album has 0 entries, I still need it! The problem is that I need to LEFT JOIN (right?) TWO tables (gallery_photo, nodes) on ONE (gallery_album), how do I do that?
View Replies !
DISTINCT With Multiple Tables And INNER JOIN ?
My query works OK this way, but I need every "kohde_name" only once (preferred the earliest "hav_date"). I've tried with "DISTINCT kohde_name" in many ways, but always end up with syntax error...:( Is there any solution with DISTINCT, or do I need anotjer solution? my (simplified) query: SELECT henk_name,henk_number, kohde_name ,kohde_txt,havainto_id,hav_kohdetxt, hav_date FROM ((henkilo INNER JOIN havaitsija ON hja_henkilo_id=henkilo_id) INNER JOIN havainto ON hja_havainto_id=havainto_id) INNER JOIN kohde ON hav_kohde_id=kohde_id WHERE ...
View Replies !
Select And Join For Multiple Tables
I have five tables in my database, there are actually NO common fields between them, not even a KEY or ID or anything like that, except for the "body" of a blob field. and that text is not identical, just a portion of that text is identical. each table has 5 fields, all different except the blob, which is called "message", so normally I use something like: select * from table1 where message like '%apple%'; to query this table, and the same goes for table 2, except the blob is different, table 2 normally is like this: select * from table2 where message like '%customerid=453%'; It's impossible to change the data in these fields (which would be the best option), but there is one common element between them in the message blob. What I want to do is something like this: select * from table1, table2 where message like '%order=100%'; however only one table will have that order, either table1 or table2, but never both, and theres no way to tell which of the tables will actually have the text. In other words, I want to search a bunch of tables for common text without having to actually submit the query five times, because the list of elements to search is about 25,000 items... I'd rather submit 25,000 queries than 125,000 queries.
View Replies !
Join Multiple Rows / Group By
I have 2 tables. table.users ------ userID (primary) username table.userEntries ------ entryID (primary) userID favorite_color favorite_game favorite_shoe and the data might like something like this .....
View Replies !
Left Join Multiple Columns
I am having problems LEFT joining Muti - columns to ONE table This is what I am using: SELECT * FROM nanny_tbl left join position_tbl on nanny_tbl.position_pos=position_tbl.recid_pos and nanny_tbl.position2_nan=position_tbl.recid_pos WHERE recid_nan = colname The first left join woks 100% the 2nd does not nanny_tbl stucture is: recid_pos --- this is primary key position_pos ---- this is VarChar Who can help me solve this?
View Replies !
Listing Multiple Rows In One Row With JOIN
I have assets and tasks in my table. There are multiple assets assigned to each task. What I want to do is to list the asset followed by all the tasks linked to it in one row. SELECT * FROM asset LEFT JOIN task ON (asset.id = task.asset_id) WHERE asset.id=task.asset_id GROUP BY asset.name The above is close, but it will only join the first matching entry from the task table, the result I was hoping to get was something like. asset1.id asset1.name task1.name task1.status task2.name task2.status Is there an easy way to do this in SQL?
View Replies !
JOIN For Display Of Multiple Arrays
My latest challenge is this: I'm still working on my "Books/Stories" Project. What I'm working out is how to display a "notes" section next to each story entry. Basically it's a comments section next to each paragraph of a story. So my plan of attack was going to be to setup my select statement with joins to get: FROM story-text TABLE storyid - which story the text belongs to blockid - which paragraph block the text is content - the actual paragraph text authorid - which author wrote this paragraph (JOIN with username from users table) dateadded - when the text was added to the db FROM story-notes TABLE storyid - which story blockid - which paragraph block content - the actual note text authorid - Who left the note (JOIN with username from users table) dateadded - when the note was added So then I could use a 'while' loop to get and display each paragraph with its notes then move to the next paragraph. BUT WAIT... There could be multiple notes per paragraph... so how do I efficiently get all the notes when I get each paragraph? I can't while loop with another select statement inside another while loop can I? I feel like this can be done best with a proper use of JOINS and the subsequent extraction and reconstruction of data, but I'm a little overwhelmed on how that would go.
View Replies !
Left Join Multiple Tables
I want to join more than one table using left join. I have a table that stores shop profile where id field is primary key. This id field is foriegn key in orders, products and customers table. Now i want to show a listing in one query. I tried following query, but its giving incorrect results. Select s.id, s.title, count(p.id), count(o.id), count(c.id) FROM shop as s LEFT Join products p on s.id = p.shop_id LEFT JOIN orders o on s.id = o.shop_id LEFT Join customer c on s.id = c.shop_id Group by s.id The results i want look like ID Title Products Orders Customers 2 Test 5 4 3 4 Test2 2 0 0
View Replies !
Join To Multiple Tables With Conditions
activities , projects, AND project_messages, project_writeboards, project_art_work ...etc the projects_messages and project_writeboards belongs to projects, and the activities model have the following fields: id, user_id, action, item_id, item_type, created_at, updated_at. im trying to do some joins to get a specific project activities from the diferent project_*models*. so this is my sql: SELECT `activities`.* FROM `activities` join project_writeboards on item_id = project_writeboards.id and item_type = "ProjectWriteboard" AND project_writeboards.project_id = 1 join project_messages on item_id = project_messages.id and item_type = "ProjectMessage" AND project_messages.project_id = 1 but i can get it to work, but if i do only one join , like: SELECT `activities`.* FROM `activities` join project_writeboards on item_id = project_writeboards.id and item_type = "ProjectWriteboard" AND project_writeboards.project_id = 1
View Replies !
Multiple LEFT OUTER JOIN
What is the right way to do a LEFT OUTER JOIN on both these tables to the 'Main' table where WHERE Plan.pID IS NULL and Record.rID IS NULL (primary's) Plan pID | _mn (foreign main key) | pType Record rID | _mn (foreign main key) | rDate Main mID (primary) | mComp ------------------------------------- Will need: pType, rDate, mComp values as a result of the query.
View Replies !
Can't Use JOIN And SUM To Roll Up Rows Into Multiple Columns
Let's say I've got the following table "Eats" that contains row after row of Calories consumed by children eating lunch at the school cafeteria. The "ID" column is an autoincrement field I added, but it doesn't seem to be doing me any good. mysql> SELECT * FROM Eats; +------+------+------+----------+----+ | dow | who | sex | Calories | ID | +------+------+------+----------+----+ | Mon | John | Boy | 2600 | 1 | | Mon | Tom | Boy | 1900 | 2 | | Mon | Jane | Girl | 1200 | 3 | | Tue | Tom | Boy | 1600 | 4 | | Tue | Jane | Girl | 1300 | 5 | +------+------+------+----------+----+ The output I WANT is a table with total calories broken down by gender and day, i.e., one (and only one) table with a "Boys" total and a "Girls" total. In this case I want to see: +------+------+-------+ | Day | Boys | Girls | +------+------+-------+ | Mon | 4500 | 1200 | | Tue | 1600 | 1300 | +------+------+-------+ It's important to have One table with Two headers, not the other way around. Not knowing any better I tried a JOIN and came closest with: mysql> SELECT a.dow AS "Day", -> SUM(a.Calories) AS "Boys", -> SUM(b.Calories) AS "Girls" -> FROM Eats AS a -> JOIN Eats AS b -> ON (a.sex="Boy" AND b.sex="Girl" AND a.dow=b.dow) GROUP BY a.dow; Which produces: +------+------+-------+ | Day | Boys | Girls | +------+------+-------+ | Mon | 4500 | 2400 | | Tue | 1600 | 1300 | +------+------+-------+ Everything is right except Mon-Girls, which is twice the real value. I figure this is due to the pairing of Jane-John and Jane-Tom, causing Jane's meal to get counted twice. But that pairing is necessary to make sure we count all the boys' Calories too. Am I overlooking something obvious? Are JOINs and SUMs just a bad idea? Most importantly, is there some other way to accomplish the task? I've tried many, many variations on the above code (with and without using the ID field) and gotten nowhere. For this simple example I could do SELECTs INTO variables, but that solution doesn't generalize to hundreds of rows, nor cases where the children are further broken down by age. Code:
View Replies !
Join Results From Select Multiple Statment
how I can join results from multiple queries in one result without geting a prodict of the tables ex: table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... table1_Col1, table1_Col2, table1_Col3...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... , , ...table2_Col1, table2_Col2, table2_Col3...table3_Col1, table3_Col2, table3_Col3... , , ... , , ...table3_Col1, table3_Col2, table3_Col3... , , ... , , ...table3_Col1, table3_Col2, table3_Col3... using the 4.0 sql so no subqueries supported.
View Replies !
Self Join (?) To Get Data For Multiple Users From One Table
I am trying to generate a report containing ebay feedback scores for multiple users. Here is the database structure for the table that contains the scores: TABLE: feedback identities_id int(10) <- contains the user id which is stored in another table feedback int(10) <- contains the feedback score for a specific date and time date_recorded datetime <- date the feedback was captured I need to get the feedback scores for let's say two users (I will want up to five but I figure the query will be almost the same) over a thirty day period. Here is the query I have created thus far (which does not return the correct results): mysql SELECT user1.feedback AS user1feedback , user2.feedback AS user2feedback , UNIX_TIMESTAMP(user1.date_recorded) AS date_recorded FROM feedback as user1 LEFT JOIN feedback as user2 ON user1.date_recorded = user2.date_recorded WHERE user1.identities_id = 1 OR user2.identities_id = 2 AND user1.date_recorded > NOW() - INTERVAL 30 DAY ORDER BY date_recorded ASC This returns virtually the same data for both users which means I am not distinguishing the two properly.
View Replies !
Multiple Left Outer Join Results Duplicates
I have tables 1. Company Company-Id | Name 1 | c-1 2 | c-2 ... 2. Company_vs_Industry Company-Id | Industry 1 | sales 1 | service 2 | sales ..... 3. Company_vs_Group Company-Id | Group 1| group-1 1| group-2 2| group-2 I am trying to select the Company with its associations with LEFT OUTER JOIN like, Select * from Company as a left outer join Company_vs_Industry as b on b.Company-Id=a.Company-Id left outer join Company_vs_Group as c on c.Company-Id=a.Company-Id and I am getting the result as, Company-Id | Name| Industry | Group 1| c-1| sales | group-1 1| c-1| sales | group-2 1| c-1| service | group-1 1| c-1| service | group-2 ... Company-Id | Name| Industry | Group 1| c-1| sales | group-1 1| c-1| service | group-2 ...
View Replies !
Select Multiple Rows With Multiple Values In A Single Statement
when selecting multiple rows with different values in a certain row.. is this proper syntax? SELECT * WHERE name IN ('hello', 'cookie', 'smile', 'police', 'fun') It seems to work fine, but It came from another SQL version I believe .. not mySQL. Just wanted to double check, or see if there is a more correct way.
View Replies !
Select / Insert Multiple Rows As A Single Row Of Multiple Columns
I have a nice database set up that contains information about orders and the items on those orders. If an order has 10 items on it, I can select the item data which returns 10 rows of data (let's say 5 colums each). Beautiful! Now I find myself needing to satisfy a program that requires all of the data on a single row. I can do this in a higher level language, but if I could accomplish it all in mysql it would be better. I don't need to sum or do any calculations. I just want to select those 5 columns of data about those 10 rows worth of items as a single row with 50 columns. For example, I'd want this: 1-1,1-2,1-3,1-4,1-5 2-1,2-2,2-3,2-4,2-5 To become: 1-1,1-2,1-3,1-4,1-5,2-1,2-2,2-3,2-4,2-5 The first complication is that the number of items on an order is variable, but is always at least 1 and can not exceed 20. The closest I've been able to get is to do something like: SELECT GROUP_CONCAT(item_number,",",qty,","",description,"",",price,",",location_number SEPARATOR ",") FROM items WHERE order_number=12345 This will give me a single text string containing the value content of the INSERT query (which will need to be manipuated outside of the SQL query to pad it with NULL values for the unused items' columns etc).
View Replies !
Select Statement With Multiple Counts/multiple Joins Trouble
SQL SELECT g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username, count(c.comment_id) as comment_count, count(v.video_id) as video_count, count(m.user_id) as user_count FROM duo_groups as g LEFT JOIN duo_group_members as m on m.group_id=g.group_id LEFT JOIN duo_users as u on u.user_id=m.user_id LEFT JOIN duo_videos as v ON g.group_id=v.group_id LEFT JOIN duo_video_comments as c on v.video_id=c.video_id GROUP BY g.group_id, g.name, g.description, g.icon, g.user_id, g.date, u.username Will return My current problem with the above statement is with the counts. The count is accurate when only one of the counts is returned. However when more than 1 of the counts is returned the other counts (if they are not 0) will return the same number. For example look at the screenshot above. The first row, all three counts are the same but they are not accurate. They alll point to 8 because there are 8 comments, but there aren't 8 videos or 8 users. Same with the second row. There aren't 2 users only 2 videos.
View Replies !
Joins With Multiple Tables And Multiple Rows
I'm making a good ol' forum, and i have three tables, users, threads and posts. when i query my threads table with a join, i need to access the users table twice to get the username of the first poster and last poster. But how? I can only figure out how to get one or the other. Is my design bad? eg SELECT TopicID, FirstPostID, LastPostID, Replies, Views, Topic, username FROM DiscussionThreads, users WHERE DiscussionThreads.FirstPostID=users.ID ORDER BY FirstPostDT DESC LIMIT 10 .
View Replies !
Select Multiple Keywords From Multiple Tables
am trying to do. I have a database called "members" that contains a table called "Reviews". This table has fields S1, S2, S3, S4, etc. that I want to search for the words ad, advertisement, and promotion. Here is the syntax I came up with that doesn't work: SELECT * FROM `REVIEWS` WHERE S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, XTRA='ad, advertisement, promotion' It would be perfectly fine, if easier, to search all fields in the table.
View Replies !
Multiple Value Query
I was wondering how to pull multiple values from a database? So that... SELECT * FROM user WHERE user_id = 2, but also numbers, 3, 4, 5, 6, etc... Do I need to redefine user? SELECT * FROM user AS user1, user AS user2 WHERE user1.user_id = 1 AND user2.user_id = 2?
View Replies !
Query Across Multiple Tables?
I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date order. So I need to write a query that retrieves all the information for one record, lets say I want to query on the main table any entry that is for the 2004-01-06 and this date is also in a field called 'Date' in the other tables, how do I go about pulling back all the info for that entry across all the tables? I have tried doing this: WHERE main.Date = table2.Date AND table3.Date AND table4.date however it returns loads of data when it should only pull back the one entry.
View Replies !
Query For Multiple Args
I have a simple array compound of user_id's. (i get this array from a query users table). then i want to get from another table, some users info. let's say it is the age of each one. should i make a query for each user_id that i have? or is there a smarter way, which does it in a single query?
View Replies !
Multiple Table Query
I'm building an entertainment/food/festival website for my community using PHP and MySQL and have hit my first "speedbump" in the process. I have a simple text form that I need to query 3 seperate tables in my database (venue_profiles, artist_profiles and events) so if someone searches for a term, it will return a numbered list of everything regardless if it is a venue/artist profile or event. Everything was working fine when I only had to query 1 table but now I've been thrown a curveball and need it to search 3. Here are the tables:fields in question. 1) venue_profiles: id, name, city, category, description 2) artist_profiles: id, name, description 3) events: id, title, city, description What will the query look like for what I need? and how do I go about creating one simple list that includes all the results from all 3 tables?
View Replies !
Getting Multiple Row Counts In One Query
I'm trying to optimize a page and reduce the number of queries used to generate it. One area where the database is being pounded is a report that takes an add and reports the number of clicks and impressions it has. That data is stored in two separate tables with each click & impression having its own record. I can successfully get the number of clicks with the query below: Code: SELECT ab.adTitle , ab.adID , ac.acctName , COUNT(c.clickIP) AS Total_Clicks FROM accounts AS ac LEFT JOIN ads AS a ON ac.acctID = a.acctID LEFT JOIN ad_banners AS ab ON a.adID = ab.adID LEFT JOIN clicks AS c ON a.adID = c.adID WHERE ac.acctID = 126 But when I try to get the number of impressions as well the value of Total_Clicks becomes the same as the value of Total_Impressions: Code: SELECT ab.adTitle , ab.adID , ac.acctName , COUNT(c.clickIP) AS Total_Clicks , COUNT(i.impIP) AS Total_Impressions FROM accounts AS ac LEFT JOIN ads AS a ON ac.acctID = a.acctID LEFT JOIN ad_banners AS ab ON a.adID = ab.adID LEFT JOIN clicks AS c ON a.adID = c.adID LEFT JOIN impressions AS i ON a.adID = i.adID WHERE ac.acctID = 126
View Replies !
|
TRACKER
