SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Could Customers be a view?

# ? Apr 12, 2010 20:55

Adbot: ADBOT LOVES YOU

# ? May 27, 2024 03:23

Squashy Nipples: Aug 18, 2007

Hammerite posted:

Could Customers be a view?

Possibly... I'm pulling this SQL from Siebol Analytics, and it keeps referring to "Subject Areas".

"Customers" is a Subject Area.

# ? Apr 12, 2010 21:11

stubblyhead: Sep 13, 2007; That is treason, Johnny!; Fun Shoe

I need to get my SQL chops back up, but of course it's pointless to just read a book without having a database to muck about with and try things out. Is there a preferred database sandbox that I could connect to or download to play around with? I'd prefer not to create something from the ground up if I don't have to.

# ? Apr 12, 2010 23:39

Sebbe: Feb 29, 2004

stubblyhead posted:

I need to get my SQL chops back up, but of course it's pointless to just read a book without having a database to muck about with and try things out. Is there a preferred database sandbox that I could connect to or download to play around with? I'd prefer not to create something from the ground up if I don't have to.

Eclipse has a pretty good sample database for BIRT.

quote:

The BIRT sample database provides a simple set of tables and data that form the basis for BIRT sample reports. The schema is for Classic Models, a retailer of scale models of classic cars. The database contains typical business data such as customers, orders, order line items, products and so on.

Sebbe fucked around with this message at 23:57 on Apr 12, 2010

# ? Apr 12, 2010 23:50

stubblyhead: Sep 13, 2007; That is treason, Johnny!; Fun Shoe

Sebbe posted:

Eclipse has a pretty good sample database for BIRT.

This looks perfect, thanks.

# ? Apr 12, 2010 23:59

GazChap: Dec 4, 2004; I'm hungry. Feed me.

I've figured out how to do this in MySQL before, but I'm buggered if I can remember how.

I've got a table that has product IDs, category IDs, and a "displayorder" field that sets the order that the products should be displayed in that category.

code:

+--------+--------+--------+
| pid    | cid    | disp   |
+--------+--------+--------+
| 1      | 1      | 1      |
| 2      | 1      | 4      |
| 3      | 1      | 5      |
| 4      | 1      | 8      |
| 5      | 2      | 1      |
| 12     | 2      | 2      |
| 24     | 2      | 3      |
| 26     | 2      | 10     |
| 27     | 2      | 11     |
| 28     | 2      | 15     |
| 30     | 4      | 1      |
| 32     | 4      | 2      |
| 34     | 4      | 3      |
+--------+--------+--------+

I would like to find a simple mySQL query that will reset this to:

code:

+--------+--------+--------+
| pid    | cid    | disp   |
+--------+--------+--------+
| 1      | 1      | 1      |
| 2      | 1      | 2      |
| 3      | 1      | 3      |
| 4      | 1      | 4      |
| 5      | 2      | 1      |
| 12     | 2      | 2      |
| 24     | 2      | 3      |
| 26     | 2      | 4      |
| 27     | 2      | 5      |
| 28     | 2      | 6      |
| 30     | 4      | 1      |
| 32     | 4      | 2      |
| 34     | 4      | 3      |
+--------+--------+--------+

I know the query revolves around using the SET statement to set a variable containing the "current" order, incrementing it each time. The bit I can't work out is how to reset the variable back to 1 when a new category ID is encountered, and indeed how to make sure that the query runs on the table in category ID order so a new category ID is only encountered after its finished with the category its doing at the moment.

Does anyone know the answer? I've done it before and meant to save the query as I knew I'd forget the chubbing thing, but never mind.

# ? Apr 13, 2010 09:38

Sebbe: Feb 29, 2004

GazChap posted:

I've figured out how to do this in MySQL before, but I'm buggered if I can remember how.

I've got a table that has product IDs, category IDs, and a "displayorder" field that sets the order that the products should be displayed in that category.

(...)

Does anyone know the answer? I've done it before and meant to save the query as I knew I'd forget the chubbing thing, but never mind.

Something like this, perhaps?

code:

SET @disp := 1, @cid := -1;
UPDATE tablename
SET disp = IF(@cid = (@cid := cid), @disp := @disp + 1, @disp := 1)
ORDER BY cid, disp ASC;

# ? Apr 13, 2010 14:34

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Alternatively, you could use a stored procedure:

code:

CREATE PROCEDURE `Renumber_Displayorder` (IN Separation TINYINT UNSIGNED)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN

DECLARE varFinished TINYINT DEFAULT 0;
DECLARE varPreviousCID INT UNSIGNED DEFAULT 0;
DECLARE varCurrentCID INT UNSIGNED;
DECLARE varCurrentPID INT UNSIGNED;
DECLARE varDisplayValue INT UNSIGNED;

DECLARE Cur CURSOR FOR
    SELECT `cid`, `pid`
    FROM `TableName`
    ORDER BY `cid`, `disp`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET varFinished = 1;

OPEN Cur;
REPEAT
    FETCH Cur INTO varCurrentCID, varCurrentPID;
    IF varFinished = 0 THEN
        IF varCurrentCID <> varPreviousCID THEN
            SET varDisplayValue = 0;
        END IF;
        SET varDisplayValue = varDisplayValue + Separation;
        SET varPreviousCID = varCurrentCID;
        UPDATE `TableName`
        SET `disp` = varDisplayValue
        WHERE `cid` = varCurrentCID AND `pid` = varCurrentPID;
    END IF;
UNTIL varFinished = 1 END REPEAT;
CLOSE Cur;

END

Then to perform the renumbering you would just issue CALL `Renumber_Displayorder`(1). Or if you wanted the numbers to be in increments of 10 you could issue CALL `Renumber_Displayorder`(10).

# ? Apr 13, 2010 17:38

MoNsTeR: Jun 29, 2002

Squashy Nipples posted:

I've just been given a query in Oracle SQL, and I'm having trouble parsing it.
code:
SELECT
 "- Profile"."Account Name" saw_1,
 "Account Team"."Employee Full Name" saw_2,
 "Account Team"."Role on Account" saw_3,
 "Account Team"."Employee Full Name" saw_4,
 "Account Team"."Role on Account" saw_5,
 "- Address".City saw_6,
 "- Address"."State/Province" saw_7,
 "- Rel Products"."Product/Service" saw_8

FROM Customers

WHERE ...
The Double-Quotes are pretty easy to figure out: they are used like Brackets [] to go around names with spaces and such. Also, not using "AS" when naming columns is pretty common.

What I don't get is the Table structure...

This query lists only ONE Table in the FROM clause, "Customers". And yet, the fields appear to be pulled from 4 different Tables? What is going on there? To my knowledge, all four tables should be listed in the FROM clause.

Maybe they are "Sub-Tables" of Customers? Like, the full name for the first field is:
Customers."- Profile"."Account Name"

If there is such a thing as Sub-Tables, then how do I join them?

If this came from Siebel Analytics a.k.a. Oracle Business Intelligence Enterprise Edition, and Customers is a subject area, then the "query" is useless to you. The objects it references exist only within OBIEE and will not be visible to someone with only direct database access. You would need to look at the OBIEE repository within the administration tool to figure out what real database objects these identifiers represent, and how they relate to each other.

# ? Apr 13, 2010 20:09

Scaevolus: Apr 16, 2007

With SQLite, what is the most efficient way to either insert a row or increment its counter it if it already exits?

# ? Apr 13, 2010 22:51

No Safe Word: Feb 26, 2005

Scaevolus posted:

With SQLite, what is the most efficient way to either insert a row or increment its counter it if it already exits?

You could potentially use ON CONFLICT REPLACE depending on the use-case.

# ? Apr 14, 2010 00:31

Flamadiddle: May 9, 2004

I'm not sure this is the best place to ask, but I'm trying to put together an extended stored procedure in C# and can't for the life of me figure out how to get input parameters... I'd assumed it was just a case of using:

code:

static void main myProcedure(String[] args){

}

Edit: Okay, so apparently the arguments have to be specified as SQL data types. That's fine, but there doesn't seem to be a SQLVarchar data type available. I can pass SQLInt32s and stuff as much as I want, but I want to get strings. Any help?

Edit2: Nevermind! There's a SQLChars object that gets strings as char arrays.

Flamadiddle fucked around with this message at 11:09 on Apr 14, 2010

# ? Apr 14, 2010 09:54

Squashy Nipples: Aug 18, 2007

MoNsTeR posted:

If this came from Siebel Analytics a.k.a. Oracle Business Intelligence Enterprise Edition, and Customers is a subject area, then the "query" is useless to you. The objects it references exist only within OBIEE and will not be visible to someone with only direct database access. You would need to look at the OBIEE repository within the administration tool to figure out what real database objects these identifiers represent, and how they relate to each other.

Yeah, this is exactly what is going on, thank you for the confirmation!

My boss gave me two different queries, each from a different "subject area", and asked me to combine them. However, it doesn't look like there is anyway to join the subject areas, so it can't be done.

Hopefully, whoever set up the DB will allow me to set up an ODBC connection, so I can access the underlying data directly. I've already had my Fill of Analytics, I really don't want to learn another reporting engine.

# ? Apr 14, 2010 14:56

FateFree: Nov 14, 2003

I need a little help analyzing this query. I've been told to optimize it (I am not a DBA), but the beauty of my job is that you can never tell if whoever wrote this (also not a DBA) is doing something extremely intelligent, or loving retarded.

So here is the (simplified - change columns to *) query:

code:

SELECT * FROM 
(SELECT * FROM RESERVATIONS WHERE PROFILE_ID = ? ORDER BY PICKUP_DATE DESC FETCH FIRST ? ROWS ONLY)
 X ORDER BY PICKUP_DATE ASC FOR READ ONLY

Sooo what I'm seeing is they are sub-selecting the top (always 10) rows by profile_id order by most recent pick_up date, and then they are just reversing the entire list. Can anyone explain what this might be doing and if theres a simpler way of doing it?

# ? Apr 14, 2010 16:55

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

So its grabbing the x newest rows and the sorting them by the oldest picked up which doesn't really make sense unless that's how he's displaying them to the user or something.

So he's either being lazy on his application side or its for a view?

# ? Apr 14, 2010 18:25

FateFree: Nov 14, 2003

Sprawl posted:

So its grabbing the x newest rows and the sorting them by the oldest picked up which doesn't really make sense unless that's how he's displaying them to the user or something.

So he's either being lazy on his application side or its for a view?

Yeah, I have a feeling its for a view. Im guessing its because there is a lack of a time_modified column so he needs something to get the newest rows and it is unfortunately the row he needs to sort the opposite way. I don't personally see the reason for showing your latest reservations backwards but thats why I'm a dev I guess. Thanks though, I'll suggest that we let the front end sort it instead.

# ? Apr 14, 2010 18:42

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

I also saw some things about Fetch First that can slow down queries and things if your keys aren't setup right Did you run it though an explain and see if its using keys properly? Because i'm not really sure how DB2 uses keys in that way but check and see if it has a key just for these 2 fields.

PICKUP_DATE , PROFILE_ID

# ? Apr 14, 2010 18:49

GazChap: Dec 4, 2004; I'm hungry. Feed me.

Sebbe posted:

Something like this, perhaps?

code:

SET @disp := 1, @cid := -1;
UPDATE tablename
SET disp = IF(@cid = (@cid := cid), @disp := @disp + 1, @disp := 1)
ORDER BY cid, disp ASC;

That looks pretty drat convincing to me, thanks

# ? Apr 15, 2010 09:37

s0meb0dy0: Feb 27, 2004; The death of a child is always a tragedy, but let's put this in perspective, shall we? I mean they WERE palestinian.

Whats the best software you guys have found for SQL development in OSX?

# ? Apr 20, 2010 02:19

Acer Pilot: Feb 17, 2007; put the 'the' in therapist

I don't know how to get this query to stop "using filesort." Anybody have any ideas?

code:

SELECT s.`pilot`, p.`name`, s.`sector`, s.`hull` 
FROM `pilots` p 
 LEFT JOIN `ships` s ON ( (s.`game` = p.`game`) 
  AND (s.`pilot` = p.`id`) ) 
WHERE p.`game` = 1 
 AND p.`id` <> 2 
 AND s.`sector` = 43 
 AND s.`hull` > 0 
ORDER BY p.`last_move` DESC

My tables:

code:

CREATE TABLE IF NOT EXISTS `pilots` (
  `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
  `game` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `last_move` int(10) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`),
  KEY `last_move` (`last_move`),
  KEY `game_id_lastmove` (`game`,`id`,`last_move`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE IF NOT EXISTS `ships` (
  `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
  `game` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `pilot` mediumint(5) unsigned NOT NULL DEFAULT '0',
  `sector` smallint(5) unsigned NOT NULL DEFAULT '0',
  `hull` smallint(4) unsigned NOT NULL DEFAULT '50',
  UNIQUE KEY `id` (`id`),
  KEY `game` (`game`),
  KEY `pilot` (`pilot`),
  KEY `sector` (`sector`),
  KEY `hull` (`hull`),
  KEY `game_2` (`game`,`pilot`,`sector`,`hull`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

The explain from MySQL:

quote:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p ref id,game_id_lastmove game_id_lastmove 1 const 7 Using where; Using filesort
1 SIMPLE s ref game,pilot,sector... game_2 6 const,fightclub_alpha.p.id,const 1 Using where; Using index

Please help me figure this out :smith:

# ? Apr 20, 2010 04:06

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

drcru posted:

I don't know how to get this query to stop "using filesort." Anybody have any ideas?

code:

SELECT s.`pilot`, p.`name`, s.`sector`, s.`hull` 
FROM `pilots` p 
 LEFT JOIN `ships` s ON ( (s.`game` = p.`game`) 
  AND (s.`pilot` = p.`id`) ) 
WHERE p.`game` = 1 
 AND p.`id` <> 2 
 AND s.`sector` = 43 
 AND s.`hull` > 0 
ORDER BY p.`last_move` DESC

My tables:

code:

CREATE TABLE IF NOT EXISTS `pilots` (
  `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
  `game` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `last_move` int(10) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`),
  KEY `last_move` (`last_move`),
  KEY `game_id_lastmove` (`game`,`id`,`last_move`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE IF NOT EXISTS `ships` (
  `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT,
  `game` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `pilot` mediumint(5) unsigned NOT NULL DEFAULT '0',
  `sector` smallint(5) unsigned NOT NULL DEFAULT '0',
  `hull` smallint(4) unsigned NOT NULL DEFAULT '50',
  UNIQUE KEY `id` (`id`),
  KEY `game` (`game`),
  KEY `pilot` (`pilot`),
  KEY `sector` (`sector`),
  KEY `hull` (`hull`),
  KEY `game_2` (`game`,`pilot`,`sector`,`hull`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

The explain from MySQL:

Please help me figure this out :smith:

KEY `game_id_lastmove` (`last_move`,`game`,`id`)

should do it i do belive

# ? Apr 20, 2010 06:55

Acer Pilot: Feb 17, 2007; put the 'the' in therapist

I had to change my indexes but I figured it out, thanks.

# ? Apr 20, 2010 12:06

Studebaker Hawk: May 22, 2004

Is there an idiots guide to SQL recovey/repair? I am running into what I am sure are all sorts of very basic errors when rebuilding logs/recovering from bak/importing and exporting data

Does anyone have any resources or suggestions?

# ? Apr 20, 2010 22:20

OneEightHundred: Feb 28, 2008; Soon, we will be unstoppable!

Kind of a higher-level design discussion:

I'm currently dealing with an ASP.NET/MSSQL application where sproc overspecialization is starting to (I think) get out of control. For example, retrieving one very commonly-used type of object (which contains references to various other objects) results in a pretty oversized sproc being run, and via joins and multiple (4) table returns, that object and a good chunk of its dependencies are also returned.

Problem of course is what fields are filled in in those other objects (and what references are filled in) are somewhat arbitrary, leading to a lot of trial-and-error to find out what is and isn't there and what needs to call additional sprocs to grab stuff from the DB.

I'm trying to make a hard push to end this, but I need an alternative. The best I can think of now is trying to get code-side objects to be 1:1 with DB rows (there is no inheritance of objects that pull from DB data so that's a non-issue), make object sprocs only store or retrieve data from one table each, resolve references on demand, cache objects code-side, and periodically flush the cache to the DB.

Is there a better overarching approach to this?

OneEightHundred fucked around with this message at 19:26 on Apr 21, 2010

# ? Apr 21, 2010 17:27

mindphlux: Jan 8, 2004; by R. Guyovich

edit nvm

# ? Apr 22, 2010 04:37

Victor: Jun 18, 2004

OneEightHundred, that sounds like an insane situation. However, it's pretty hard to give advice with the level of detail you've provided — I'm not sure I could say anything except for your own ideas on improving things. Is there any way you could come up with a mock schema that would reasonably mirror your actual schema? Moreover, how are the data returned actually being used in code? By the way, you're going to want to be able to turn caching off, and you will probably want to be able to force a cache flush of all sessions.

You know about table-valued UDFs, right? Those may be useful in your situation. Also remember cross apply.

# ? Apr 22, 2010 21:48

Thirteenth Step: Mar 3, 2004

I'm having a horrible time creating a cascade delete between tables. (using phpmyadmin)

I have 2 tables, 'staff' and 'staff_shift'.

'staff' contains details of staff members and 'staff_shift' is a link table between 'staff' and 'shift' and references staff members against shifts worked all tables are innoDB.

What i'm trying to do is create a cascade delete so that when a staff member is deleted, all records of shifts that have are deleted from the staff_shift table but im getting the following error;

quote:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`database`.`#sql-d10_452`, CONSTRAINT `#sql-d10_452_ibfk_1` FOREIGN KEY (`staffid`) REFERENCES `staff_shift` (`staffid`) ON DELETE CASCADE)

Im going into the staff table --> relation view and then selecting staff_shift.staffid from the staffid field.

I don't suppose anyone knows whats going on? That literally the best I can describe it. Sorry.

# ? Apr 22, 2010 22:07

Victor: Jun 18, 2004

Give us the SQL that sets this all up. Three create tables, the necessary foreign keys, some inserts with dummy data, and then the offending delete. (Feel free to omit most of the columns in the table definitions.)

# ? Apr 22, 2010 22:14

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Thirteenth Step posted:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`database`.`#sql-d10_452`, CONSTRAINT `#sql-d10_452_ibfk_1` FOREIGN KEY (`staffid`) REFERENCES `staff_shift` (`staffid`) ON DELETE CASCADE)

I think you have declared your foreign key "the wrong way round", i.e. in the wrong table. Your CREATE TABLE statement for staff_shift should look something like this

code:

CREATE TABLE `staff_shift` (
    ...
    CONSTRAINT `Constr_staff_shift_staff_fk`
        FOREIGN KEY `staff_fk` (`Staff`) REFERENCES `Staff` (`StaffID`)
        ON DELETE RESTRICT ON UPDATE CASCADE
)

If the CONSTRAINT... FOREIGN KEY bit is in the CREATE TABLE statement for staff then you have put it in the wrong table.

i.e. the reason you are getting this error when trying to delete a staff member is MySQL sees that there are entries in staff_shift corresponding to that staff member and concludes that the row should not be deleted

# ? Apr 22, 2010 22:28

Thirteenth Step: Mar 3, 2004

Victor posted:

Give us the SQL that sets this all up. Three create tables, the necessary foreign keys, some inserts with dummy data, and then the offending delete. (Feel free to omit most of the columns in the table definitions.)

Ok here goes;

(edited out SQL dump, fixed in next post)

Thats my dump of my SQL export, seems like there's a bit missing... that's all I can find. Sorry about not having a clue, im fairly new to this, havent touched phpmyadmin in a good 2 years.

Thirteenth Step fucked around with this message at 22:51 on Apr 22, 2010

# ? Apr 22, 2010 22:30

Victor: Jun 18, 2004

You can remove `dept` to `news`; they are irrelevant for the question at hand.
You haven't included any foreign key declarations.

# ? Apr 22, 2010 22:41

Thirteenth Step: Mar 3, 2004

Victor posted:

You can remove `dept` to `news`; they are irrelevant for the question at hand.

You haven't included any foreign key declarations.

Sorry

code:

...

I was under the impression that eg;

code:

KEY 'shiftid' ('shiftid')

was representing the foreign keys?

Thirteenth Step fucked around with this message at 23:22 on Apr 22, 2010

# ? Apr 22, 2010 22:56

Victor: Jun 18, 2004

Nope, those KEY statements are apparently indexes, according to MySQL's CREATE TABLE documentation. Think about it: your KEY statements aren't referencing the primary tables!

# ? Apr 22, 2010 23:01

Thirteenth Step: Mar 3, 2004

Hammerite posted:

I think you have declared your foreign key "the wrong way round", i.e. in the wrong table. Your CREATE TABLE statement for staff_shift should look something like this

I did just this :gonk:

thanks a lot

# ? Apr 22, 2010 23:22

rugbert: Mar 26, 2003; yea, fuck you

I've just started learning SQL and PHP and I was wondering how to pull info from my tables based on some specific criteria. Like, heres my table:

code:

+---------+---------+-----------------+----------------------+
| meta_id | post_id | meta_key        | meta_value           |
+---------+---------+-----------------+----------------------+
|      58 |      28 | Bedrooms        |  1                   |
|     192 |      60 | Bedrooms        |  3                   |
|     152 |      28 | Price           |  600                 |
|     163 |      60 | Price           |  900                 |
|     175 |      28 | Utilities       |  Water/sewage/trash  |
|     186 |      60 | Utilities       |  Electic, Heating    |
|     163 |      62 | Price           |  800                 |
|      58 |      62 | Bedrooms        |  1                   |
+---------+---------+-----------------+----------------------+

What I want to do is get the post_id when all search criteria is met. So if I was searching for bedrooms=1 and price>= 900 it would grab post_id of 28 and 62.

So far when I try searching for multiple things like that it get empty sets even tho I know I have them.

I was thinking of something like:
SELECT * FROM wp_postmeta WHERE (meta_key = 'Price' AND meta_key BETWEEN 1 AND 900) AND (meta_key = 'Bedrooms' AND meta_key = '1'); but that gives me an empty set.

# ? Apr 22, 2010 23:40

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

rugbert posted:

I was thinking of something like:
SELECT * FROM wp_postmeta WHERE (meta_key = 'Price' AND meta_key BETWEEN 1 AND 900) AND (meta_key = 'Bedrooms' AND meta_key = '1'); but that gives me an empty set.

Is one of those ANDs supposed to be an OR?

# ? Apr 22, 2010 23:45

Nebulis01: Dec 30, 2003; Technical Support Ninny

I'm new to managing a SQL infrastructure and am trying to automate some tasks. I'm attempting to restore (overwrite) a database and keep getting an error.

code:

RESTORE DATABASE SNOHD
From DISK = N'D:\SNOHD_backup_201004211800.bak'
WITH File = 1,
MOVE N'Snohd' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SNOHD.mdf'
MOVE N'Snohd_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SNOHD_log.ldf'
NOUNLOAD,
REPLACE,
STATS = 10
GO

Results in the following error

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'MOVE'.

I've done some googling and can't seem to pin down why I'm retarded. Any help would be awesome.

# ? Apr 22, 2010 23:59

Victor: Jun 18, 2004

You need commas at the end of your MOVE lines.

# ? Apr 23, 2010 00:01

rugbert: Mar 26, 2003; yea, fuck you

Hammerite posted:

Is one of those ANDs supposed to be an OR?

Um, yea changed to OR. Ok so now it gave me a

code:

+---------+---------+----------+------------+
| meta_id | post_id | meta_key | meta_value |
+---------+---------+----------+------------+
|     191 |      60 | Bedrooms | 1          |
|     185 |      73 | Bedrooms | 1          |
|       5 |       3 | Price    | 560        |
|     193 |      60 | Price    | 750        |
|     187 |      73 | Price    | 645        |
|     214 |      80 | Price    | 800        |
+---------+---------+----------+------------+

So in that table, a couple entries have the same IDs, 60 and 73 so those are all matches for my search. How can I get those IDs out?

Im trying to pass 60 and 73 into a variable so that way I can get all data where post_id=60 or 73

# ? Apr 23, 2010 00:05

Adbot: ADBOT LOVES YOU

# ? May 27, 2024 03:23

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

rugbert posted:

So in that table, a couple entries have the same IDs, 60 and 73 so those are all matches for my search. How can I get those IDs out?

Im trying to pass 60 and 73 into a variable so that way I can get all data where post_id=60 or 73

So are you saying that you're only interested in the values in post_id and you want to get distinct values back? Change SELECT * to SELECT DISTINCT post_id.

From your second paragraph it sounds like you want to identify post_ids found in rows satisfying particular criteria, then get back all of the rows in which those post_ids appear irrespective of whether they caused the post_id to be included. One way you could do this is by issuing

code:

SELECT * FROM table_name WHERE post_id IN (
    SELECT DISTINCT post_id FROM TableName WHERE [where conditions]
)

# ? Apr 23, 2010 00:24

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »